Inner Join with Top 1 for single Row

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 2005 Forums

Transact-SQL (2005)

Inner Join with Top 1 for single Row

Author

Topic

Heinz23
Yak Posting Veteran

84 Posts

Posted - 2009-04-01 : 08:10:08

Hi all,
my question is is quite similar to this one http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97772 but with one difference so I'll explain:
I have 2 tables (in fact I have 3 but I guess the 3rd one follows the same logic like the first 2 so it should be enough to clarify with just 2 tables).

Table A:

UserID     Username
1          John
2          Paul
3          Ringo
4          George

Table B:

UserId     RecordDate    Units
1          31.03.2009    50
2          31.03.2009    70
1          17.03.2009    90
3          14.03.2009    20

I want to write a SELECT which returns the following table

UserId     Username      Units
1          John          50
2          Paul          70
3          Ringo         20 
4          George

So for each user only the latest record from Table B should be used (Record from Table B from 17.03. for User 1 is ignored as there is a newer one for this Userid from 31.03.). With a single select I could use TOP 1, that's fine, but how could I do this with an INNER JOIN?

many thanks in advance!

matty
Posting Yak Master

161 Posts

Posted - 2009-04-01 : 08:12:58

If SQL version is 2005 or above ,Use ROW_NUMBER .

theboyholty
Posting Yak Master

226 Posts

Posted - 2009-04-01 : 09:40:29

Try this.

SELECT A.UserID,A.Username,B.RecordDate
FROM TABLEA A
INNER JOIN (SELECT UserID,MAX(RecordDate) AS RecordDate 
FROM TABLEB B GROUP BY UserID) B ON A.USERID=b.USERID

Here i've created a derived table (aliased as B) from Table B which only includes the most recent date record from each userid.

The result is:
1 John 2009-03-31 00:00:00.000
2 Paul 2009-03-31 00:00:00.000
3 Ringo 2009-03-14 00:00:00.000

theboyholty
Posting Yak Master

226 Posts

Posted - 2009-04-01 : 09:41:59

UPDATE:

Change it to a LEFT JOIN, rather than INNER JOIN as i've just noticed you wanted to return all rows from A

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-04-01 : 09:46:21

May be this too?

select t.userid,t.username,r.units from
@t t left join
(select userid,recorddate,units from
(select userid,recorddate,units, row_number() over (partition by userid order by recorddate desc) as rn
from @r) t1
where t1.rn = 1
) r on t.userid = r.userid

theboyholty
Posting Yak Master

226 Posts

Posted - 2009-04-01 : 09:49:18

Sorry, i'm such a dufus - I really should have read your post better, i didn't include the field for Units.

Heinz23
Yak Posting Veteran

84 Posts

Posted - 2009-04-01 : 13:37:56

Hi all,

thanks for your support! I've now used the proposal from vijayisonly (as I was too confused by the comments of theboyholty

) and this works brillant, many thanks to you all!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-01 : 13:53:06

another way


SELECT A.UserId,A.UserName,B.Units 
FROM TABLEA A
OUTER APPLY (SELECT TOP 1 Units
             FROM TABLEB
             WHERE UserId =A.UserId 
              ORDER BY RecordDate DESC) B

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources