SQL Server equivalent of Postgres query

Question

select column 
from table 
order by row_number() over () 
limit 1000 offset 200;

I have the above query in PostgreSQL. I want the SQL Server equivalent of the above. I tried the one below:

select top 1000 offset 200 column 
from table 
order by row_number() over (ORDER BY column ASC) 
limit 1000 offset 200;

but it throws the following error.

Incorrect syntax near '17000000'.

mendosi · Accepted Answer · 2016-11-07 05:01:21Z

2

SELECT column 
  FROM table 
  ORDER BY Row_Number() Over (Order By column)
  OFFSET 200 ROWS
  FETCH NEXT 1000 ROWS ONLY;

https://technet.microsoft.com/en-us/library/gg699618(v=sql.110).aspx

Note that you should really use an ORDER BY clause that is guaranteed to give the same order each time.

answered Nov 7, 2016 at 5:01

mendosi

2,0611 gold badge14 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

marc_s Over a year ago

And to use OFFSET....FETCH in SQL Server, you must define an explicit ORDER BY ....

UV. · Accepted Answer · 2016-11-07 05:05:14Z

1

some databases will not return anything for INFORMATION_SCHEMA.COLUMNS with offset 200 but I put it here for the sake of working code.

SELECT t.TABLE_NAME, t.COLUMN_NAME 
    , ROW_NUMBER() over (order by t.COLUMN_NAME asc) as RowNumber
FROM INFORMATION_SCHEMA.COLUMNS as t
ORDER BY ROW_NUMBER() over (order by t.COLUMN_NAME asc)
OFFSET 200 ROWS
FETCH NEXT 1000 ROWS ONLY

answered Nov 7, 2016 at 5:05

UV.

4926 silver badges9 bronze badges

Collectives™ on Stack Overflow

SQL Server equivalent of Postgres query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related