SELECT TOP N and BOTTOM N Rows Using SQL Server

If you have been looking out for a query that gives you TOP 'N' and Bottom 'N' records of a table, then check this out.

The query shown below selects the TOP 5 and BOTTOM 5 records ordered by CustomerID from the 'Customers' table of the Northwind database:

;With CTETemp

as

(

Select

CustomerID

,ROW_NUMBER() OVER (Order BY CustomerID) as TopFive

,ROW_NUMBER() OVER (Order BY CustomerID Desc) as BottomFive

FROM

Customers

)

Select CustomerID From CTETemp Where TopFive <=5 or BottomFive <=5

ORDER BY TopFive asc

Result:

Customer ID

ALFKI

ANATR

ANTON

AROUT

BERGS

WARTH

WELLI

WHITC

WILMK

WOLZA