CODEWITHSUNDEEP
sqlsql-serversql-order-byoffset
eagertoLearn
eagertoLearn

Reputation: 10162

SQL Offset losing its order in SQL Server

I have a sorted temp table called #MytempTable The sorting was based on not just a specific Column, but many other factor

Now i want to use OFFSET for the Pagination

OFFSET @PageSize*(@PageNumber-1) Row Fetch Next @PageSize Rows Only

But if I do it like this on my temp table as below

Select * 
from #MytempTable 
ORDER BY ( SELECT NULL) OFFSET
@PageSize*(@PageNumber-1) Row Fetch Next @PageSize Rows Only

The Order that I had on the temp table is lost.

I want to preserve the order of the temp table after the offset.

Upvotes: 0

Views: 458

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

There is no order on a table. Not even on a "temp table".

This is a fundamental principle of SQL: tables (and result sets with no ORDER BY) represent unordered sets. Period. Ordering information is captured in the data, using column(s) in the data.

A result set with no ORDER BY can be returned in any order -- and on different orders on subsequent executions.

When you add ORDER BY (SELECT NULL) you are explicitly stating: "I have to put an ORDER BY here but I don't care about the ordering." That extends to: "I don't care if it is the same on subsequent executions.

You can preserve ordering when you create a temporary table by using an identity column. For instance:

select identity(int) as temp_id, . . .
into #temp
from . . . 
order by <whatever you want here>;

Then you can recover the ordering by using order by temp_id when you query the table.

Upvotes: 1

DatabaseCoder
DatabaseCoder

Reputation: 2032

Order is never guaranteed in the results set unless you specify Order By in the query. Even if you want the result set ordered by the clustered key, it may not be returned in that order due to many factors.

In your case, I think you can add one new identity(auto-populate) column to your temp table and at the time of select you should use that column with Order By clause instead of Order By (select null).

Upvotes: 1

Related Questions