How do you get the values of rows 10-20 in T-SQL?

Is it possible to get the values of just rows 10 开发者_JAVA技巧through 20? If so how?

If you're using SQL Server 2005 or greater, check out the ROW_NUMBER function: http://msdn.microsoft.com/en-us/library/ms186734.aspx

One way is to do something like the following...

SELECT * FROM (
    SELECT TOP x * FROM (
        SELECT TOP y fields
        FROM table
        WHERE conditions
        ORDER BY table.field  ASC) as foo
    ORDER by field DESC) as bar
ORDER by field ASC

x is the number of rows you want returned and y is x+offset.

http://josephlindsay.com/archives/2005/05/27/paging-results-in-ms-sql-server/

Hey, by the asnwer of joelt about row_number(). I did it. Its like this:

SELECT allianceId, position, points from (select ROW_NUMBER() over (Order by Points DESC) as position, points, allianceId from Alliance) as somethingx where position >= @alliancePosition - 5 and position <= @alliancePosition + 5;