Solved

SQL SELECT DISTINCT (Need WHOLE record, not field)

Posted on 2007-04-08
6
2,161 Views
Last Modified: 2012-06-22
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
Comment
Question by:pegasys
6 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18872524
Can you post a sample of you data and indicate the desired result?
0
 
LVL 29

Expert Comment

by:Nightman
ID: 18872541
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
 
LVL 7

Author Comment

by:pegasys
ID: 18872862
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 18872977
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
 
LVL 18

Expert Comment

by:JR2003
ID: 18873201
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now