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

select
        o.*
from 
        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

select 
       
*
from 
        northwind
..orders as o
where 
       
(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

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

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: , ,

Comments

# 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