Solved

SQL SELECT DISTINCT (Need WHOLE record, not field)

Posted on 2007-04-08
6
2,166 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

622 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