CODEWITHSUNDEEP
sql-serverpostgresqllimitoffset
Kabhi
Kabhi

Reputation: 135

SQL Server equivalent of Postgres query

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'.

Upvotes: 1

Views: 202

Answers (2)

UV.
UV.

Reputation: 492

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

Upvotes: 1

mendosi
mendosi

Reputation: 2051

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.

Upvotes: 2

Related Questions