Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Return TOP N rows

Sep 12 2008 3:39PM by Madhivanan   

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

Tags: t-sql, sql_server, top-n,


Madhivanan
3 · 40% · 12958
2
 
0
Lifesaver
 
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"Return TOP N rows" rated 5 out of 5 by 2 readers
Return TOP N rows , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]