Selecting top 1 of each in a join

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

Transact-SQL (2008)

Selecting top 1 of each in a join

Author

Topic

kensai
Posting Yak Master

172 Posts

Posted - 2011-04-11 : 06:29:42

I have the following to table


[Persons]
PersonId Name
-------- ------
1        John
2        James

[Orders]
OrderId PersonId OrderDate
------- -------- ---------
1       1        01.01
2       1        02.01
3       2        01.01
4       2        03.01

What I want to do is join Persons and Orders but get only the latest order of the person. This is the result I want:


PersonId Name OrderId OrderDate
-------- ---- ------- ---------
1        John  2       02.01
2        James 4       03.01

How can I do this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-04-11 : 06:48:48

SELECT p.PersonID, p.Name, f.OrderID, f.OrderDate
FROM (SELECT OrderID, PersonID, OrderDate, ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY OrderDate DESC) AS YakID FROM dbo.Orders) AS f
INNER JOIN dbo.Persons AS p ON p.PersonID = f.PersonID
WHERE f.YakID = 1

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources