SQL SELECT DISTINCT (Need WHOLE record, not field)

MSSQL:

If I have a table with about 7000 records. One of the fields is called 'personname' and I want a 'DISTINCT' list of all the people I would: SELECT DISTINCT personname from TheTable

THe problem is that I dont want the one field back, I want the entire record.

What I'm trying to do is cleanse a database of duplicate people and write it into a new table. i.e.

SELECT DISTINCT personname, * INTO CLeanTable FROM TheTable

Problem is, it doesnt work like this :-( I just need one record per person.

Can someone help me with this?
LVL 7
pegasysAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
Can you post a sample of you data and indicate the desired result?
0
NightmanCTOCommented:
Sorry - That shouldn't have been an administrative comment (and as such, cannot be accepted as a solution).

pegasys, did that solve your problem? If not perhaps you can post a sample as acperkins suggested.
0
pegasysAuthor Commented:
TableToCLeanOut

id     name     info
1      Peter      Bla bla
2      Mark      something bla
3      Peter      woohoo
4      Karl      other data
5      Peter      more info

What I need is something that brings back:


id     name     info
1      Peter      Bla bla
2      Mark      something bla
4      Karl      other data



I dont care which record it brings back, I just needf ONE (any one) of the records whwere the name field (peter) was repeated. I just want to eliminate old records where people changed details and it was duplicated.

- A
0
Anthony PerkinsCommented:
Than go with Nightman solution (no points for me please) except do it as follows:
SELECT MIN(ID),
             personname,
             MIN(nfo)
INTO     CLeanTable
FROM TheTable
GROUP BY
             personname
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JR2003Commented:
How do you know that you are removing the right row?
Maybe you want to select the row with the max Id?

This query will do that quite neatly using an inline query:

SELECT T1.Id, T1.PersonName, T1.nfo
   FROM TheTable T1
 INNER JOIN (SELECT Max(T2.Id), T2.PersonName
               FROM TheTable T2
              GROUP BY T2.PersonName) AS T3
         ON T3.Id = T1.Id

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.