10

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).

  • 10
    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. – redcalx Oct 23 '12 at 20:50
  • 1
    so why not ask that question? – Aaron Bertrand Oct 23 '12 at 21:32
14

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.

| improve this answer | |
  • 6
    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
22

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.

| 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. – redcalx Oct 23 '12 at 15:09
  • 22
    The only way to guarantee an order in SQL is to ask for it – Remus Rusanu Oct 23 '12 at 15:14
0

Actually the (old) question could be extended to also include the partition by part, as there seems to be an implicit order by first the partition by and then the order by part(s)

So it is not quite as simple as some has suggested as to assign the row_number and use that for ordering.

We would need a nested sql extracting the partition by clause as well (if we do not want to simply repeat it of course)

Empirically we seem to get the last line of ordering implicitly

Select
  <field-list>, 
  rn=Row_Number() over(partition by <PartionClause>) order by <OrderClause>
from ....
Order by <PartionClause> asc, rn asc

But what we lack is any proof or guarantee that it always holds.

(If there are multiple Row_Number() calls in play, it is the LAST one that seems to give the ordering)

ALSO NOTE if you explicitly add the order by the execution plan clearly shows a final sort-step, and sorting an already sorted set is kind of worst-case, hence it takes substantially longer with the Order By than without

| improve this answer | |
  • 1
    This doesn't really answer the question, and it seems to disagree with both the accepted answer, and a highly upvoted answer. – Erik Darling Jan 3 '18 at 13:59
  • I agree that mine is more a comment than an answer. Well the accepted answer isn't really an answer, except it says that we better not trust the order - and that was what the question was. It can be seen by the execution plan that it actually does a sort, and if we add an explicit order by, we get a second sort. But that is NOT a proof, it is only part of the empirical knowledge – Eske Rahn Jan 3 '18 at 14:17
  • (Of course it only does the sorts if there aren't a suitable index it can use to access the table, but only added it to show that it is not generally cost-free to explicitly give the order by) – Eske Rahn Jan 3 '18 at 14:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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