Remove duplicates from outut - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Remove duplicates from outut

Author

Topic

ch9862
Yak Posting Veteran

76 Posts

Posted - 2007-02-14 : 11:34:01

[code]declare @a table (id int, cde varchar(4))
insert @a
select 1, 'ZZ10' union all
select 2, 'DS20' union all
select 3, 'ZZ19' union all
select 4, 'ZZ28' union all
select 5, 'AI01'

declare @b table (id int, nme varchar(6), dt datetime)
insert @b
select 1, 'AV209', '10/11/2004' union all
select 2, 'CI101', '03/28/2001' union all
select 2, 'GH331', '08/01/2004' union all
select 3, 'AT202', '10/09/2006'[/code]

Tables can be joined on id.
I need to retrieve cde from table @a, plus nme and dt from table @b, but limit the output to one record per id, with the most recent dt displayed.

I'm expecting:
[code]id nme dt
1 AV209 10/11/2004
2 GH331 08/01/2004
3 AT202 10/09/2006
4 NULL NULL
5 NULL NULL[/code]

So I thought I'd do it by left joining @a and @b - but how do I
filter out duplicate ids?

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-02-14 : 11:46:05

[code]SELECT a.cde
,a.id
,b.nme
,b.dt
FROM @a AS a
LEFT JOIN @b AS b
JOIN (SELECT id, MAX(dt) dt FROM @b GROUP BY id) AS mx
ON mx.id = b.id
ON b.id = a.id
AND b.dt = mx.dt[/code]

Mark

ch9862
Yak Posting Veteran

76 Posts

Posted - 2007-02-14 : 14:07:41

Thanks for the answer!

How does it work? Why are there two JOINs before two ONs?

Confused :)

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-02-15 : 03:51:48

No probs. Okay, it works as follows:

1. As you worked out in your original post, you need a LEFT JOIN to @b to ensure that you get all rows from @a.

2. The

(SELECT id, MAX(dt) dt FROM @b GROUP BY id)

is a derived table that gives us the latest dt for each id.

3. The nested join ensures that the right side of the join is constrained to only those rows from @b that have the latest date (i.e. INNER JOIN to the derived table in 2. above).
If you performed this join outside the outer join, it would contrain the whole query and you would end up with just the three rows that exist in @b.
Hope that makes some sense!

Mark

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-15 : 04:08:58

I think he is referring to the

SELECT       a.cde 
            ,a.id
            ,b.nme
            ,b.dt
FROM         @a AS a
LEFT JOIN    @b AS b 
    JOIN     (SELECT id, MAX(dt) dt FROM @b GROUP BY id) AS mx
    ON       mx.id = b.id
ON           b.id = a.id
    AND      b.dt = mx.dt

versus

SELECT       a.cde 
            ,a.id
            ,b.nme
            ,b.dt
FROM         @a AS a
LEFT JOIN    @b AS b ON b.id = a.id
INNER JOIN   (SELECT id, MAX(dt) dt FROM @b GROUP BY id) AS mx ON mx.id = b.id AND b.dt = mx.dt

Peter Larsson
Helsingborg, Sweden

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-02-15 : 06:10:33

Precisely

Mark

SQL_Rookie
Starting Member

32 Posts

Posted - 2007-02-15 : 07:59:00

what if on table @b there were more rows like
declare @b table (id int, nme varchar(6), dt datetime)
insert @b
select 1, 'AV209', '10/11/2004' union all
select 1, 'BB209', '11/11/2004' union all
select 2, 'CI101', '03/28/2001' union all
select 2, 'GH331', '08/01/2004' union all
select 3, 'AT202', '10/09/2006'

and we wanted to get the max dt per id but wanted the date to be greater then 09/01/2004....so basically id 2 will not be in the result set.....how can you do that?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-15 : 08:02:43

[code]
select id, max(dt)
from @b b
where dt > '20040901'
group by id
[/code]

SQL_Rookie
Starting Member

32 Posts

Posted - 2007-02-15 : 08:05:16

But I meant joining to the @a table as well. Just like the first poster "mwjdavidson" mentioned. I just wanted to see how you could filter out from the original query the ID =2 by using the dates
so expecting something like

id nme dt
1 BB209 11/11/2004
3 AT202 10/09/2006
4 NULL NULL
5 NULL NULL

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-02-15 : 08:35:13

You can just add a WHERE clause:

WHERE b.dt > '20040901' OR  b.dt IS NULL

Mark

ch9862
Yak Posting Veteran

76 Posts

Posted - 2007-02-15 : 11:03:03

quote:
Originally posted by mwjdavidson
Hope that makes some sense!

It does - thanks! Took me a while to understand, but I got it once I played with Peso's notation:

LEFT JOIN    @b AS b ON b.id = a.id
INNER JOIN   (...)
-- VS
LEFT JOIN    @b AS b
   INNER JOIN   (...)
ON b.id = a.id

Now I've heard of nested JOINs - thanks again to both of you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources