Adrian Sluijters
asked on
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?
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?
Can you post a sample of you data and indicate the desired result?
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.
pegasys, did that solve your problem? If not perhaps you can post a sample as acperkins suggested.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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