Solved

SQL SELECT DISTINCT (Need WHOLE record, not field)

Posted on 2007-04-08
6
2,165 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

710 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