Select database rows in range

Question

I want to select the rows between A and B from a table. The table has at least A rows but it might have less than B rows. For example if A = 2, B = 5 and the table has 3 rows it should return rows 2 and 3.

How could I get the rows in such a range?

I am using Microsoft SQL Server 2008.

Answer 1

You can use something similar to what's being described in this SO question.

I.E.

SELECT * FROM ( 
  SELECT *, ROW_NUMBER() OVER (ORDER BY YOUR_ORDERED_FIELD) as row FROM YOUR_TABLE
 ) a WHERE row > 5 and row <= 10

Where A = 5 and B = 10 in your example.

Answer 2

SELECT *,ROW_NUMBER() OVER 
    (ORDER BY ordercol) AS 'rank'
FROM table
where rank between @a and @b