Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL SELECT statement that ignores duplicate records

Posted on 2006-04-23
19
Medium Priority
?
310 Views
Last Modified: 2008-03-06
Okay so I have search everywhere and either don't get it or am not using the correct terminology.  I am trying to write a SELECT statement to select all of the records in a database, except for the duplicates.  The table have columns such as firstname, lastname, SSN, order number.  So the table with data would look like this:

firstname  lastname  SSN                   order number
-------------------------------------------------------------
bob           smith       111-22-3333        125675
jerry         jones       333-22-4444         789439
bob           smith       111-22-3333         848493
kari           taylor       123-45-6789         435435
bob           smith       111-22-3333         256999
kari           taylor       123-45-6789         443435

I want to compare the records off of the SSN column and just get a recordset like the following:

firstname  lastname  SSN        
----------------------------------------
bob           smith       111-22-3333
jerry         jones       333-22-4444
kari           taylor       123-45-6789


Can someone please help me with this?  I have tried several different ways according to other websites and I either get all the records returned or only one.  Thanks much!!
0
Comment
Question by:pgreetis
19 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1200 total points
ID: 16521823
Hi pgreetis,

Method 1

SELECT firstName, lastname,SSN,OrderNumber
FROM urTable
WHERE SSN in
(SELECT DISTINCT SSN FROM urTable)

Aneesh R!
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16521831
Method 2

SELECT T1.firstName, T1.lastname,T1.SSN,T1.OrderNumber
FROM urTable T1
INNER JOIN
(SELECT DISTINCT SSN FROM urTable) T2
ON T1.SSN = T2.SSN
0
 

Author Comment

by:pgreetis
ID: 16521846
Thanks Aneesh R!, but when I tried that it just gives me the total number of records.  Any other ideas?
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16521862
ok ,i got it, U ned to replace the ssn in the inner query using OrderNumber, hoping that it is unique
0
 

Author Comment

by:pgreetis
ID: 16521887
So tried this:

SELECT firstName, lastname,SSN,OrderNumber
FROM urTable
WHERE SSN in
(SELECT DISTINCT OrderNUmber FROM urTable)

AND

SELECT firstName, lastname,SSN,OrderNumber
FROM urTable
WHERE OrderNUmber in
(SELECT DISTINCT OrderNumber FROM urTable)


And got 0 records returned.  I feel like such an idiot because I know this should be so easy...
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16521900


SELECT T1.firstName, T1.lastname,T1.SSN,T1.OrderNumber
FROM urTable T1
INNER JOIN
(SELECT MAX(OrderNumber)  OrderNumber FROM urTable GROUP BY SSN) T2
ON T1.OrderNumber = T2.OrderNumber
0
 
LVL 17

Assisted Solution

by:ZeonFlash
ZeonFlash earned 400 total points
ID: 16521928
Does distinct in the main select not work?

SELECT DISTINCT firstName, lastName, SSN
FROM theTable
0
 
LVL 17

Assisted Solution

by:HuyBD
HuyBD earned 400 total points
ID: 16521940
Try this!

SELECT firstName, lastName, SSN,MAX(OrderNumber)
FROM yourtable
GROUP BY firstName, lastName, SSN
0
 

Author Comment

by:pgreetis
ID: 16521942
Okay one problem.  The excel file I am running this SQL SELECT statement against has a column that has a space in the name (ie, Customer SNN), so if I set the statement like so:

SELECT T1.firstName, T1.lastname,T1.'Customer SSN',T1.OrderNumber
FROM urTable T1
INNER JOIN
(SELECT MAX(OrderNumber)  OrderNumber FROM urTable GROUP BY 'Customer SSN') T2
ON T1.OrderNumber = T2.OrderNumber

OR

SELECT T1.'firstName', T1.'lastname',T1.'Customer SSN',T1.'OrderNumber'
FROM urTable T1
INNER JOIN
(SELECT MAX('OrderNumber')  'OrderNumber' FROM urTable GROUP BY 'Customer SSN') T2
ON T1.'OrderNumber' = T2.'OrderNumber'

I get an error message.  This is in VB6 by the way.

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16521944
ZeonFlash,

Of course this will work, but if you wanna select the records based on the Ordernumber, i think it wont work
0
 

Author Comment

by:pgreetis
ID: 16521946
To HuyBD and ZeonFlash,

When I try those ways I only get one record returned.  Thanks those.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16521956
Put a square bracket instead of Quotes

SELECT T1.firstName, T1.lastname,T1.'Customer SSN',T1.OrderNumber
FROM urTable T1
INNER JOIN
(SELECT MAX(OrderNumber)  OrderNumber FROM urTable GROUP BY [Customer SSN]) T2
ON T1.OrderNumber = T2.OrderNumber
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16521960
pgreetis,
> When I try those ways I only get one record returned.  Thanks those.

HuyBD's solution will work

SELECT firstName, lastName, SSN,MAX(OrderNumber)OrderNumber
FROM urTable
GROUP BY firstName, lastName, SSN
0
 

Author Comment

by:pgreetis
ID: 16521979
Both ways are compaining about a Syntax Error (missing operator) in query expression

For the one from HuyBD it says 'MAX(OrderNumber)OrderNumber'

The one you showed above said the same thing about this part: MAX(OrderNumber)  OrderNumber
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16521992
declare @tab table (firstName varchar(32),lastname varchar(33), SSN varchar(32), OrderNumber int)

Insert into @tab
select 'bob',           'smith',       '111-22-3333',        125675

Insert into @tab
select 'jerry' ,        'jones'   ,    '333-22-4444'  ,       789439
Insert into @tab
select 'bob',           'smith',       '111-22-3333',         848493

SELECT firstName, lastName, SSN,MAX(OrderNumber)OrderNumber
FROM @tab
GROUP BY firstName, lastName, SSN
0
 

Author Comment

by:pgreetis
ID: 16522003
The only problem with that solution is that the excel file will never be the same other than the column names and data types.  I will not be able to run the above Declare and Insert every time I want to work with the file.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16522022
i just given an example, to show that it is working
0
 

Author Comment

by:pgreetis
ID: 16522029
Gotcha!  Unfortunately that still doesn't solve my problem.  If I don't get a working solution together by the end of the day tomorrow I will award the points to Aneesh R
0
 

Author Comment

by:pgreetis
ID: 16524778
Okay so I don't know what happened between last night and this morning but I tried this:

SELECT DISTINCT([Customer SSN]) FROM urTable

just on a whim and it is magically working!!  I thought I had tried this but I guess not.  I am going to split the points as everyone did technically provide working solutions, but I am giving a larger share to Aneesh R as he put a lot into this.  Thanks again everyone!!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone 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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

810 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