• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2168
  • Last Modified:

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?
0
pegasys
Asked:
pegasys
1 Solution
 
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
 
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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now