Returning rows between two row numbers (Between Range)

Question

UsersTbl Structure:

id | uname
 __  ____
1  | name0
2  | name1
4  | name2
1  | name3
8  | name4
5  | name5
6  | name6

which kind of query that i'm looking for:
query that selects every row between row number 2 to row number 5 (without using the id column)
example:

//start = 2
//end = 5
select * from users where rownum >= start and where rownum <= end

results should be:

name2,name3,name4,name5
syntax? (mssql ,note:i'm using classic asp (Not really matters))

i couldn't use google before, now i found it...: http://stackoverflow.com/questions/4552769/sql-rownum-how-to-return-rows-between-a-specific-range — jhon, Oct 31 '14 at 04:15

score 2 · Answer 1 · answered Oct 31 '14 at 04:20

You can use ROW_NUMBER to generate a row number based on the order you have shown. You can then use that in the WHERE clause.

For example:

SELECT
    id,
    uname,
    ROW_NUMBER() OVER (ORDER BY uname) AS rownum
FROM UsersTbl 
WHERE   
(rownum >= 2) AND 
(rownum <= 6)
ORDER BY uname

Sandesh · Answer 2 · 2014-10-31T05:59:16.130

1

This query works in SQL Serevr 2008 r2

with summary 

as(

select ROW_NUMBER () over (Order by uName) as rn,UNAME

from [USE])

select uname

from summary S

WHERE S.rn >2 AND S.rn <= 6

edited Oct 31 '14 at 05:59

answered Oct 31 '14 at 04:47

Sandesh

Arunprasanth K V · Answer 3 · 2014-11-12T07:03:50.393

0

SELECT id,uname,ROW_NUMBER() OVER (ORDER BY uname) AS Rownumber
FROM UsersTbl 
WHERE   
(rownumber >= 2) AND (rownumber <= 6)

try this i think it will work

edited Nov 12 '14 at 07:03

answered Oct 31 '14 at 06:20

Arunprasanth K V

3 Answers3