To select any range of records from SQL Server database

Question

I'm trying to show only a selected number of rows from the database(say from 20 to 45) I'm trying to use limit but it is not working

Select * 
from UsersTable 
limit 20,45

It is wrong since SQL Server doesn't allow this feature.

The answer that I found is

SELECT * 
FROM 
    (SELECT 
         *, ROW_NUMBER() OVER (ORDER BY name) AS row 
     FROM 
         sys.databases) a 
WHERE 
    row > 20 and row <= 45

Can someone simplify this? I am not able to follow the above query, what is (ORDER BY name) in it

Say my database has the columns Id, UserName, Email and values in my Id column will be like 1, 2, 4, 8, 11, 17 -> not continuous values

Answer 1

In SQL-Server 2012 and above you could use OFFSET and FETCH in following:

SELECT *
FROM tbl
ORDER BY name
OFFSET 20 ROWS 
FETCH NEXT 25 ROWS ONLY 

In older versions you have to use ROW_NUMBER() in following:

SELECT * 
FROM ( 
    SELECT *, ROW_NUMBER() OVER (ORDER BY name) as rn 
    FROM tbl
 ) x 
 WHERE rn > 20 and rn <= 45

Answer 2

The ORDER BY name clause in your call to ROW_NUMBER is the ordering logic which will be used for assigning row numbers. Consider the following data, and the corresponding row numbers:

name | row
Abby | 1
Bob  | 2
...  | ...
Jack | 20
John | 21
...  | ...
Mike | 45

You may visualize the subquery you aliased as a as the above intermediate table. Then, you subquery a on the condition that row > 20 and row < 45. In the above table, this means you would be retaining the records from John up to an including Mike.

Later versions of SQL Server also support OFFSET and FETCH, but you should learn how to use ROW_NUMBER, perhaps the most basic analytic function, because it will likely come up again in your future work.

Your full query:

SELECT *
FROM 
(
    -- this subquery corresponds to the table given above
    SELECT *, ROW_NUMBER() OVER (ORDER BY name) AS row 
    FROM sys.databases
) a 
-- now retain only records with row > 20 and row <= 45
WHERE 
    row > 20 AND row <= 45;