Return TOP N rows

The TOP Clause returns top rows from the table based on the number or percentage value
What if you want to have TOP N rows for each group?
The following explains it

The purpose is to return top 3 orders for each customer based on the
most recent orderdate from the table Orders in Northwind database

1 Use IN

        northwind..orders as owhere orderdate in 
        (select top 3 orderdate from northwind..orders 
        where customerid=o.customerid order by orderdate desc
        )order by customerid, orderdate desc

2 Dynamically generate serial number for each customer

..orders as o
(select count(*) from northwind..orders where customerid=o.customerid 
        and orderdate>=o.orderdate)<=3
        order by customerid,orderdate desc

3 Use Row_number() function

select * from
        select *, row_number() over(partition by customerid order by customerid,orderdate desc) as sno 
        from northwind..orders
) as t
where sno<=3

4 Use UDF and Cross Apply Operator

function dbo.top_orders
@customerid nchar(10),
@limit int
returns table
        select top (@limit) orderdate from northwind..orders 
        where customerid=@customerid order by orderdate desc

select distinct o.* from northwind..orders as o
cross apply dbo.top_orders(o.customerid,3) as t
where o.orderdate=t.orderdate
order by customerid,orderdate desc

Published 12 September 2008 15:39 by Madhivanan
Filed under: , ,


# Top n records per category | keyongtech

18 January 2009 16:27 by Top n records per category | keyongtech

Pingback from  Top n records per category | keyongtech

# final cumulative value | keyongtech

Pingback from  final cumulative value | keyongtech

# first row from a table... - dBforums

01 September 2009 10:33 by first row from a table... - dBforums

Pingback from  first row from a table... - dBforums