Select unique record for each person based on 1 column

Question

I have data like this. Where sometimes there are 2 records, 1 with a mailing address & a non-mailing address, sometimes there is only 1 record and it could be either a mailing address or a non-mailing address.

UniqueID,FirstName,LastName,DOB,House Number,City,State,Mailing

4444,George,Jetson,10/10/55,800,Orbit City,Space,0    
4444,George,Jetson,10/10/55,555,Orbit City,Space,1    
5555,Fred,Flintstone,12/12/04,88,Bedrock,PH,0    
5555,Fred,Flintstone,12/12/04,100,Bedrock,PH,1    
6666,Barney,Rubble,7/7/07,999,Bedrock,PH,0    
7777,Jonny,Quest,5/30/64,343,Action City,KS,1

I'm trying to make a query that will return 1 row for each person and prefer the mailing address if it exists. So ideally the query would return these records

4444,George,Jetson,10/10/55,555,Orbit City,Space,1    
5555,Fred,Flintstone,12/12/04,100,Bedrock,PH,1    
6666,Barney,Rubble,7/7/07,999,Bedrock,PH,0    
7777,Jonny,Quest,5/30/64,343,Action City,KS,1

Does anyone have any suggestions, based on some of the articles I've been reading I am thinking maybe I need to have a subquery? I was getting stuck at the OVER PARTITION BY part in the examples I was reading, or should I have some sort of IF statement? I'm kind of new to SQL, so thanks for any direction or help.

possible duplicate of [Retrieving the last record in each group](http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) — Bulat, Sep 19 '14 at 23:07

Tim Schmelter · Answer 1 · 2014-09-19T23:00:45.467

With SQL-Server you can use ROW_NUMBER, for example with a CTE:

WITH CTE AS
(
   SELECT UniqueID, FirstName, LastName, DOB, [House Number], City, State, Mailing,
          rn = ROW_NUMBER() OVER (PARTITION BY UniqueID  ORDER BY Mailing DESC)
   FROM dbo.TableName
)
SELECT UniqueID, FirstName, LastName, DOB, [House Number], City, State, Mailing,
FROM CTE
WHERE rn = 1

Here's a fiddle: http://sqlfiddle.com/#!3/886b0/5/0

UNIQUEID    FIRSTNAME   LASTNAME    DOB HOUSE NUMBER    CITY    STATE               MAILING
4444        George  Jetson  October, 10 1955 00:00:00+0000  555 Orbit City  Space   1
5555        Fred    Flintstone  December, 12 2004 00:00:00+0000 100 Bedrock PH      1
6666        Barney  Rubble  July, 07 2007 00:00:00+0000 999 Bedrock PH              0
7777        Jonny   Quest   May, 30 1964 00:00:00+0000  343 Action City KS          1

I would just say `order by Mailing desc`. – Gordon Linoff Sep 19 '14 at 22:49 — Gordon Linoff, Sep 19 '14 at 22:49

score 2 · Accepted Answer · answered Sep 19 '14 at 22:50

2

You can also express this query as:

select *
from tablename t
where mailing = 1
union all
select *
from tablename t
where not exists (select 1 from tablename t2 where t2.uniqueid = t.uniqueid);

answered Sep 19 '14 at 22:50

Gordon Linoff

1,242,037
58
646
786

Thanks, UNION ALL is exactly what I was looking for, sorry it took a while to mark an answer but I wanted to make sure it worked for me and I got off track on this project ... – Gary N Oct 14 '14 at 04:13

Select unique record for each person based on 1 column

2 Answers2