CODEWITHSUNDEEP
sqlsql-serverwindow-functionssql-order-by
Matt Fenwick
Matt Fenwick

Reputation: 49095

What is the result set ordering when using window functions that have `order by` components?

I'm working on a query on the SEDE:

select top 20 
  row_number() over(order by "percentage approved" desc, approved desc), 
  row_number() over(order by "total edits" asc), 
  *
from editors 
where "total edits" > 30

What is the ordering of the result set, taking into account the two window functions?

I suspect it's undefined but couldn't find a definitive answer. OTOH, results from queries with one such window function were ordered according to the over(order by ...) clause.

Upvotes: 3

Views: 909

Answers (2)

Krzysztof Kozielczyk
Krzysztof Kozielczyk

Reputation: 5937

The results can be returned in any order.

Now, they will often be returned in the same order as specified in the OVER clause, but this is just because SQL Server is likely to pick a query plan that sorts the rows to calculate the aggregate. This is by no means guaranteed, as it could pick a different query plan at any time, especially as you make your query more complex which extends the space of possible query plans.

Upvotes: 5

JNK
JNK

Reputation: 65167

The result set of ANY SQL Server query that doesn't have an explicit ORDER BY is undefined.

This includes when you have window functions within the query, or an ORDER BY in a subquery. The result order will depend on a lot of factors, none of which are guaranteed unless you specify an ORDER BY.

Upvotes: 5

Related Questions