Friday, March 9, 2012

opposite of "select top" > "select last"

hi all,

i can select the first three records, by using the "select top 3 ....."

but what if i want the three last records?

i know i can workaround it by first doing an ORDER BY DESC and then doing the TOP command.... but isn't there another (easier) way?

thanks

Select TOP is the only method (easy method too). It’s all depends on your sorting only. Having a DESC & TOP is not bad idea to get the last/bottom n records.

In SQL Server 2005, we can use the ROW_NUMBER function to achieve this.

(Alternate for TOP).

Here the sample,

Code Snippet

;With CTE

as

(

Select

Name

,ROW_NUMBER() OVER (Order BY name) as ForTop

,ROW_NUMBER() OVER (Order BY name Desc) as ForBottom

from

Sysobjects

)

Select Name From CTE Where ForBottom <=3 --ForTop<=3

No comments:

Post a Comment