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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
226 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
226 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
226 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
52326 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  
 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.05 seconds. Powered By: Snitz Forums 2000