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!!
pgreetisAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
Hi pgreetis,

Method 1

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

Aneesh R!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
pgreetisAuthor Commented:
Thanks Aneesh R!, but when I tried that it just gives me the total number of records.  Any other ideas?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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
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...
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
ZeonFlashCommented:
Does distinct in the main select not work?

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

SELECT firstName, lastName, SSN,MAX(OrderNumber)
FROM yourtable
GROUP BY firstName, lastName, SSN
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.

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
pgreetisAuthor Commented:
To HuyBD and ZeonFlash,

When I try those ways I only get one record returned.  Thanks those.
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
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
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
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
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.
Aneesh RetnakaranDatabase AdministratorCommented:
i just given an example, to show that it is working
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
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!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.