Search This Blog

Monday, August 29, 2011

SELECT TOP N and BOTTOM N Rows Using SQL Server

With CTETempas(Select
ContractNumber
,ROW_NUMBER() OVER (Order BY ContractNumber) as TopFive ,ROW_NUMBER() OVER (Order BY ContractNumber Desc) as BottomFiveFROM Contracts)Select ContractNumber From CTETemp Where TopFive <=5 or BottomFive <=5
ORDERBY TopFive asc


actual table:

ContractNumberCustAddIdRenewalDateRenew ContractValueDaysUntilRen
1112/8/2008 0:001300-994
221/15/2009 0:000280-956
331/14/2009 0:000280-957
441/21/2009 0:001300-950
55NULLNULL49.99NULL
661/16/2009 0:001280-955
779/15/2008 0:001260-1078
881/3/2009 0:000280-968
992/2/2009 0:001300-938
10101/16/2009 0:001280-955
111112/18/2008 0:00NULL280-984
12123/9/2009 0:001280-903
13135/9/2009 0:000300-842
14146/21/2009 0:000280-799
15159/11/2008 0:001260-1082
16163/16/2009 0:001260-896
17177/27/2008 0:000280-1128
18181/2/2009 0:001280-969
19192/2/2009 0:001300-938
20201/31/2009 0:001280-940
21212/4/2009 0:001280-936

No comments:

Post a Comment