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

Question

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.

`OFFSET-FETCH` is applied only on *ordered* sets. In a SQL table there is no inherent order and you *have* to use `ORDER BY` if you want to specify one. Hence, what you want is simply not possible. — Giorgos Betsos, Jun 22 '15 at 13:24
Yes, of course, although I don't understand what you mean by *because my sort order will be lost*. There is no inherent sort order in the table to be lost. — Giorgos Betsos, Jun 22 '15 at 13:36
You need to specify an `ORDER BY` as there is no guarantee the current order will always be the order the results are returned in. — Mark Sinkinson, Jun 22 '15 at 13:53
ORDER BY 1 is NOT ordering by the scalar value of 1, it would be ordering by the ordinal position. That means it would order by the first column in your result set. This is a terrible habit to get in to. When you use ordinal positions you open yourself up to future bugs when the order of the columns in the select changes and you don't update the ordinal references. — Sean Lange, Jun 22 '15 at 14:00
[`ORDER BY`](https://msdn.microsoft.com/en-us/library/ms188385.aspx): "The order in which rows are returned in a result set are not guaranteed unless an ORDER BY clause is specified." — Damien_The_Unbeliever, Jun 22 '15 at 14:00
Or to put it another way, if you haven't put *something* into `@TempTable` that you can put in an `ORDER BY`, then you have *already* "lost" your sort order. — Damien_The_Unbeliever, Jun 22 '15 at 14:15
Couldn't you just add an identity(1,1) column to your @temptable definition to be used for the ordering? — Me.Name, Jun 22 '15 at 15:02
Thanks for all guides.I edited my question and now is cleared. — Mohsen Tavoosi محسن طاوسی, Jun 22 '15 at 15:07
According to comments and research, I answered my own question with tested query.Please vote up and mark as useful question and answer if you feel was useful.Thanks guys — Mohsen Tavoosi محسن طاوسی, Jun 22 '15 at 15:11
@Mohsen.Unlimited Why would the sort order be lost when using identity(1,1)? The identity column would be ascending in the order in which the records are added. — Me.Name, Jun 22 '15 at 15:39
Wow.Yes! this is great! with this way i can ignore row_number.It is better and faster.you can complete my answer and write your answer with temp table structure.Can i use temp table variable with identity column? — Mohsen Tavoosi محسن طاوسی, Jun 22 '15 at 16:15
Yep, you can use table variable with identity column. At least I've done so in sql 2012, don't know if it was always available. — Me.Name, Jun 22 '15 at 16:53

score 68 · Answer 1 · answered Jul 05 '17 at 07:12

68

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

answered Jul 05 '17 at 07:12

Jörg

803
6
7

[See this chapter from T-SQL Querying, section *The OFFSET-FETCH filter*](https://www.microsoftpressstore.com/articles/article.aspx?p=2314819) – nkr May 08 '18 at 09:11
5

You are a SQL ninja! – Kyle Johnson Apr 30 '19 at 15:06
What isn't working on SQL Server 2017? Errors? Wrong results? We are using it with this SQL Server version without problems so far. – Jörg Aug 23 '19 at 11:06
2

I've found that this creates inconsistent orderings between queries. So if you use this for pagination you might e.g. get the same row twice, etc. – Cully Jun 09 '20 at 23:04
Had to remove my upvote from accepted answer after reading your answer – Aneeq Azam Khan Aug 20 '20 at 07:58
1

I fixed mine by using ```ORDER BY 1 ``` followed by ```OFFSET .... ```. I think this means order by the first field in the query as per this thread https://stackoverflow.com/questions/3445118/what-is-the-purpose-of-order-by-1-in-sql-select-statement – user2728841 Oct 30 '20 at 11:22

score 19 · Answer 2 · answered Mar 22 '16 at 14:20

19

You cannot avoid using the required syntax of a ORDER BY with OFFSET and FETCH.

It is however possible to disassociate the ORDER BY clause that you must provide in order to perform paging from the natural table order created by the record insert process.

Using the solution below you do not have to make any changes to the underlying table either

Select 0 as TempSort, T.* From MyTable T ORDER BY TempSort OFFSET 2 ROWS FETCH NEXT 3 ROWS

answered Mar 22 '16 at 14:20

1

That's a great pure and immediate SQL SELECT fix, with no temporary tables, identity columns or other SQL Server specifics. For all those people arguing RDBMS vendors are right to force into us usage of ORDER BY: you're wrong! Simple immediate paging is supposed to get a frame between offset n and offset m of the data set no matter how it's ordered by a platform. Remember these data sets still always return rows in the same order for a specific vendor, even if ORDER BY was not specified at all! Forcing us to use ORDER BY is not always right. – Cristian Scutaru Apr 22 '16 at 16:04

Me.Name · Accepted Answer · 2015-06-23T08:55:15.883

By adding an identity column to the temp table variable

    declare @TempTable table([some columns], rownr int identity(1,1) )

    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

An automatic incrementing number is added for each row, in the order in which they are added to the temp table. The inserts don't need to fill this column, so the inserts can remain as they are. The identity column can then be used for the order by:

 select * from @TempTable Order by rownr OFFSET 20 ROWS FETCH NEXT 50 ROWS ONLY

score 1 · Answer 4 · answered Sep 21 '17 at 04:09

Offset/Fetch requires an order by clause. You can use the CURRENT_TIMESTAMP to bypass this requirement if you don't want to go by any order. I am not sure but, this should return rows based on the order of storage (clustered index maybe)

So changing your code to this should solve the issue -

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 **order by current_timestamp** OFFSET 20 ROWS FETCH NEXT 50 ROWS ONLY

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

4 Answers4

Linked