T-SQL - How to get values from row 10 to 20?

Question

is this possible using T-SQL?

Answer 1

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/

Answer 2

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

Answer 3

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;