How to use OFFSET and Fetch without Order by in SQL Server

sql-server sql-server-2012 sql-order-by fetch offset
Mohsen Tavoosi محسن طاوسی picture Mohsen Tavoosi محسن طاوسی · Jun 22, 2015 · Viewed 16.6k times · Source

I want use OFFSET and Fetch in my SQL server 2012 query.But without any order by.I can not use order by.Because my sort order will be lost. How can I use OFFSET and Fetch without order by and row number and where in my query? My 2 select tables have same structure.

INSERT INTO @TempTable [some columns]  
select [some columns] from table1 order by col1 
INSERT INTO @TempTable [same columns]
select [some columns] from table2 order by col2
select * from @TempTable OFFSET 20 ROWS FETCH NEXT 50 ROWS ONLY

This query has syntax error at OFFSET keyword.

Answer

Jörg picture Jörg · Jul 5, 2017

There is an even simpler way of providing a dummy ORDER BY clause:

select * from @TempTable ORDER BY(SELECT NULL) OFFSET 20 ROWS FETCH NEXT 50 ROWS ONLY

Related questions

How to check if a column exists in a SQL Server table?

I need to add a specific column if it does not exist. I have something like the following, but it always returns false: IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTableName' AND COLUMN_NAME = 'myColumnName') How can …

Read More
Auto increment primary key in SQL Server Management Studio 2012

How do I auto increment the primary key in a SQL Server database table, I've had a look through the forum but can't see how. I've looked the the properties but can't see an option, I have seen an answer …

Read More
Convert Date format into DD/MMM/YYYY format in SQL Server

I have a query in SQL, I have to get a date in a format of dd/mm/yy Example: 25/jun/2013. How can I convert it for SQL server?

Read More