43

I found a bunch of questions on this topic with nice solutions but none of them actually deal with what to do if the data is not to be ordered in one specific way. For instance, the following query:

WITH MyCte AS 
(
    select   employee_id,
             RowNum = row_number() OVER ( order by employee_id )
    from     V_EMPLOYEE 
    ORDER BY Employee_ID
)
SELECT  employee_id
FROM    MyCte
WHERE   RowNum > 0

works well if the data is to be ordered by employee_id. But what if my data does not have any specific order but the row numbers themselves act as an ID? My goal is to write a query like this (with the Row_Number() function having no ORDER BY clause):

WITH MyCte AS 
(
    select   employee_id,
             RowNum = row_number() OVER ( <PRESERVE ORIGINAL ORDER FROM DB> )
    from     V_EMPLOYEE 
    ORDER BY Employee_ID
)
SELECT  employee_id
FROM    MyCte
WHERE   RowNum > 0

EDIT: Upon Googling, I figured out that this is not really possible. Can some suggest a workaround for this?

Adriano Carneiro
  • 57,693
  • 12
  • 90
  • 123
Legend
  • 113,822
  • 119
  • 272
  • 400
  • 5
    Unless you have an explicit `ORDER BY` clause, there is **NO** implicit ordering (there is no "original order from DB") in a SQL Server environment – marc_s Jun 17 '11 at 18:44
  • @marc_s: Thank you. Just to clarify, do you mean to say, in a small database of say, 20000 records, it does not preserve insertion order (even though there are no concurrent inserts)? – Legend Jun 17 '11 at 18:45
  • 1
    @marc_s: +1 You are right. Even during sequential inserts, it does not preserve order. This is quite different from my MySQL experience. Thank you for the heads up! – Legend Jun 17 '11 at 18:57

5 Answers5

96

Just in case it is useful to someone else. I just figured it out from elsewhere:

WITH MyCte AS 
(
    select   employee_id,
             RowNum = row_number() OVER (ORDER BY (SELECT 0))
    from     V_EMPLOYEE 
    ORDER BY Employee_ID
)
SELECT  employee_id
FROM    MyCte
WHERE   RowNum > 0
Jacob van Lingen
  • 8,989
  • 7
  • 48
  • 78
Legend
  • 113,822
  • 119
  • 272
  • 400
  • If you feel this is the appropriate answer, you should mark it as such. That way, the question will be properly flagged as answered and others can find this solution more successfully. – mtazva Jun 17 '11 at 18:45
  • @mtazava: Actually, I would like to give it some time (anyways it has a requirement of waiting 2 days before accepting my own answer :) ). The problem is, I am not an expert in SQL Server and am hearing a few people say that the approach itself is not to be followed because the order cannot be guaranteed. – Legend Jun 17 '11 at 18:50
  • 8
    ORDER BY (SELECT 0) does not guarantee the correct ordering. See http://stackoverflow.com/questions/9737131/sql-server-join-in-order – Egor4eg Oct 23 '12 at 08:31
  • Yes, a simple way to prove that this doesn't work is by replacing `V_EMPLOYEE` by an ordered derived table: http://stackoverflow.com/q/18961789/521799 – Lukas Eder Sep 23 '13 at 14:35
  • what does `(ORDER BY (SELECT 0))` really means? – ManirajSS Apr 22 '15 at 15:14
  • For what it's worth, anything static works for "0". You can use NULL, 1, 2, 'StackOverflow', etc. – Scott R. Frost Jul 12 '16 at 13:43
23

I use this to suppress the sort:

ORDER BY @@rowcount

@@rowcount is constant within the query. Example:

select N = row_number() over (order by @@rowcount) from sys.columns

Use of (select 0) in the ORDER BY seems to run much slower.

kaborka
  • 231
  • 2
  • 3
6

The real problem with the approach that you are proposing is that order in the db is not guaranteed. It may coincidentally be returning to your application in the same order all of the time, but the SQL Standard guarantees no such order and may change depending on version or edition changes. The order of data from a SQL Server is not guaranteed without an order by clause. This design would be one that simply relies on 'luck.' If this possible variation in order has an impact on your implementation, you may want to change it now before you get too far into the implementation.

Good article on this topic

doug_w
  • 1,330
  • 10
  • 10
  • @Legend: I added a link to an article which addresses the topic of result set ordering from SQL Server. – doug_w Jun 17 '11 at 18:55
4

There is no such thing as ORIGINAL ORDER. SQL server cannot guarantee order of rows if you don't specify ORDER BY. You may get lucky and get results in particular order, but it may change any time.

Tomas Kirda
  • 8,347
  • 3
  • 31
  • 23
2

There's a solution that is simpler than above. You can still use ROW_NUMBER but supply an arbitrary value in the Order by clause: 

Select firstName, lastName, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) from tblPerson
Zphunk
  • 33
  • 4