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

SQL SELECT statement that ignores duplicate records

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
pgreetis
Asked:
pgreetis
3 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hi pgreetis,

Method 1

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

Aneesh R!
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
pgreetisAuthor Commented:
Thanks Aneesh R!, but when I tried that it just gives me the total number of records.  Any other ideas?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Aneesh RetnakaranDatabase AdministratorCommented:
ok ,i got it, U ned to replace the ssn in the inner query using OrderNumber, hoping that it is unique
0
 
pgreetisAuthor Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:


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
 
ZeonFlashCommented:
Does distinct in the main select not work?

SELECT DISTINCT firstName, lastName, SSN
FROM theTable
0
 
HuyBDCommented:
Try this!

SELECT firstName, lastName, SSN,MAX(OrderNumber)
FROM yourtable
GROUP BY firstName, lastName, SSN
0
 
pgreetisAuthor Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
ZeonFlash,

Of course this will work, but if you wanna select the records based on the Ordernumber, i think it wont work
0
 
pgreetisAuthor Commented:
To HuyBD and ZeonFlash,

When I try those ways I only get one record returned.  Thanks those.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
pgreetisAuthor Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
pgreetisAuthor Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
i just given an example, to show that it is working
0
 
pgreetisAuthor Commented:
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
 
pgreetisAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

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