Search code examples
sqlsql-serversql-order-byoffset

How to order by a different column and then fetch offset rows in SQL Server?

Consider the query below.

Select * 
From table 
Where name = 'stackoverflow' 
Order By age

This is the query I am interested in. However I want to combine this with limit and offset as well. So this is what I did now.

Select
    *, 
    ROW_NUMBER() OVER (ORDER BY primary_id DESC) as ROW_NUMBER
From 
    table 
Where 
    name = 'stackoverflow' 
Order By 
    age, 
Offset 10 rows Fetch Next 20 Rows Only

The problem is that I am getting wrong results. I want to first query all rows based on where name = 'stackoverflow' and then order By age and then only fetch some rows based on limit and offset.

Solution

  • You have two order by clause perhaps you just need one :

    select t.*
from table t 
where name = 'stackoverflow' 
order by age 
offset 10 rows 
fetch next 20 rows only;