TSQL Partition by with Order by

Question

I noticed that when I use a partition by like below

SELECT
ROW_NUMBER() OVER(PARTITION BY categoryid
ORDER BY unitprice, productid) AS rownum,
categoryid, productid, productname, unitprice
FROM Production.Products;

the result set returned to me accordingly in the proper partiton and order.

Does that mean I do not have to provide an Order BY clause at the end to absolutely guarantee the order that I want?

Thanks

Answer 1

No.

To guarantee the result order, you must use an ORDER BY that applies to the outermost query. Anything else is just coincidence.

SELECT
ROW_NUMBER() OVER(PARTITION BY categoryid
ORDER BY unitprice, productid) AS rownum,
categoryid, productid, productname, unitprice
FROM Production.Products
ORDER BY categoryid,unitprice,productid;

ORDER BY has two roles:

• To actually define how another feature works. This is true when using TOP, say, or within an OVER() partition function. It doesn't require sorting to occur, it just says "this definition only makes sense if we consider the rows in the result set to occur in a particular order - here's the one I want to use"
• To dictate the sort order of the result set. This is true when it's an ORDER BY clause on the outermost statement that is part of a particular query - not in a subquery, a CTE, an OVER() paritition function, etc.

Occasionally you will experience it being used in both senses at once - when the outermost statement includes a TOP or OFFSET/FETCH, then it's used for both purposes.