Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

E.g. consider the SQL query:

SELECT
   A.[Name],
   ROW_NUMBER() OVER(ORDER BY A.[Name] ASC)
FROM
   [FooTable] AS A

Here I observe the results being returned sorted by A.[Name]. If I change the sort column defined in the ROW_NUMBER function to another column then again the results become sorted by that column.

I was expecting the row number to be assigned to the rows but I was not expecting the rows to come back sorted by that same criteria. Is this simply a side effect of how the query is being executed (in my case on SQL Server 2008 R2) or is this behaviour guaranteed? (I could find no reference to such a guarantee).

share|improve this question
8  
There is never any guarantee. Ever. Unless you tell SQL Server how to order something, it is free to return the data in whatever order it wishes. By leaving out the order by, you are telling SQL Server you don't care about the order. If you want a guarantee, just type the ORDER BY clause already. –  Aaron Bertrand Oct 23 '12 at 18:02
    
There's a good reason for asking as the order-by clause in question is very complex and therefore copying creates both code maintenance ballast as well as increasing the probability of future defects. The other alternative is a minor refactoring which I thought I might be able to avoid if the SQL standard defined an implicit sort order. In any case I was just interested if there was formal documentation covering this point. –  locster Oct 23 '12 at 20:50
1  
so why not ask that question? –  Aaron Bertrand Oct 23 '12 at 21:32

2 Answers 2

up vote 8 down vote accepted

If you had asked the question I think you actually meant to ask:

How can I order by ROW_NUMBER() without repeating the complex ORDER BY expression?

We could have told you to create an alias for the ROW_NUMBER() expression, and then sort using the alias:

SELECT
   A.[Name],
   rn = ROW_NUMBER() OVER (ORDER BY <complex expression>)
FROM
   dbo.[FooTable] AS A
ORDER BY rn;

Now you only have to change the expression in one place, and the resulting sort will be based on that expression without having to repeat it.

share|improve this answer
5  
In fact, repeating the complex ORDER BY expression doesn't even guarantee the output will come out by ROW_NUMBER() order. Unless the columns in the ORDER BY clause form a unique key. –  孔夫子 Oct 24 '12 at 2:54

Absolutely not. Proof:

SELECT
   A.[Name],
   ROW_NUMBER() OVER(ORDER BY A.[Name] ASC),
   ROW_NUMBER() OVER(ORDER BY A.[Name] DESC)
FROM
   [FooTable] AS A

The only way to guarantee an order in SQL is to ask for it, use ORDER BY on the result itself.

share|improve this answer
    
OK I'll add one further qualification - are the results guaranteed to be sorted if only one ROW_NUMBER() clause is specified. –  locster Oct 23 '12 at 15:09
21  
The only way to guarantee an order in SQL is to ask for it –  Remus Rusanu Oct 23 '12 at 15:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.