How to select top N number per date group?

I have 1 table named BookMe,

with fields

$Date, $Div, $Name

I want to select the top N number of Div's (ie highest Div value) per distinct Date.

i know how to get the distinct date like this:

SELECT DISTINCT $Date FROM BookMe ORDER BY $Date

How do I combine a TOP keyword to get the TOP n rows per distinct date?

Thx!

[376 byte] By [ted.tea] at [2007-12-24]

«« Any XNA books on the horizon?
»» Does WDS update last access times of files that it scans and indexes?

# 1

For the highest value you could just say:

SELECT DISTINCT $Date, MAX($Div) [Highest]

FROM BookMe

GROUP BY $Date

ORDER BY $Date

WhitneyWeaver at 2007-10-8 >

top of Msdn Tech,SQL Server,Transact-SQL...

# 2

SQL Server 2005:

(top 1 $Div for each date)

SELECT t1.[$Date], t1.[$Div], t1.[$Name] FROM (SELECT [$Date], [$Div], [$Name], RANK() OVER(PARTITION BY [$Date] ORDER BY [$Div] DESC) as RowNum

FROM yourTable) t1

WHERE t1.RowNum <2

limno at 2007-10-8 >

# 3

erm...

I'm looking for plain SQL (and not tied to any platform per se)...

any others please... please..

ted.tea at 2007-10-8 >

# 4

SELECT [$Date], [$Div] FROM (SELECT [$Date], [$Div], (SELECT count(*) FROM yourTable as t2

WHERE t2.[$Date] = t1.[$Date] and t2.[$Div] >= t1.[$Div]) AS RowNum

FROM yourTable as t1

) t3

WHERE t3.RowNum<2--change to 3 if you want top 2 in each $Date group

limno at 2007-10-8 >

# 5

You can also write the query using standard SQL like:

use Northwind

-- Get 2 latest orders for each customer:

select o1.*
from Orders as o1
join (select o2.CustomerID, max(o2.OrderDate) as od1 from Orders as o2 group by o2.CustomerID) as o3
on o3.CustomerID = o1.CustomerID
and o1.OrderDate in (o3.od1
, (select max(o4.OrderDate)
from Orders as o4
where o4.CustomerID = o1.CustomerID and o4.OrderDate < o3.od1)
)

And depending on your indexes / data, the query above might perform better than the count(*) or row_number version.

UmachandarJayachandran-MS at 2007-10-8 >

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech