SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Inner Join with Top 1 for single Row
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Heinz23
Yak Posting Veteran

84 Posts

Posted - 04/01/2009 :  08:10:08  Show Profile  Reply with Quote
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 - 04/01/2009 :  08:12:58  Show Profile  Reply with Quote
If SQL version is 2005 or above ,Use ROW_NUMBER .
Go to Top of Page

theboyholty
Posting Yak Master

United Kingdom
221 Posts

Posted - 04/01/2009 :  09:40:29  Show Profile  Visit theboyholty's Homepage  Reply with Quote
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
Go to Top of Page

theboyholty
Posting Yak Master

United Kingdom
221 Posts

Posted - 04/01/2009 :  09:41:59  Show Profile  Visit theboyholty's Homepage  Reply with Quote
UPDATE:

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

vijayisonly
Flowing Fount of Yak Knowledge

USA
1836 Posts

Posted - 04/01/2009 :  09:46:21  Show Profile  Reply with Quote
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
Go to Top of Page

theboyholty
Posting Yak Master

United Kingdom
221 Posts

Posted - 04/01/2009 :  09:49:18  Show Profile  Visit theboyholty's Homepage  Reply with Quote
Sorry, i'm such a dufus - I really should have read your post better, i didn't include the field for Units.

Go to Top of Page

Heinz23
Yak Posting Veteran

84 Posts

Posted - 04/01/2009 :  13:37:56  Show Profile  Reply with Quote
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!!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/01/2009 :  13:53:06  Show Profile  Reply with Quote
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    
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000