To select any range of records from SQL Server database [duplicate]

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