6

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
TheWommies
  • 4,922
  • 11
  • 61
  • 79

1 Answers1

12

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.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • +1 the `ORDERBY` inside the `OVER()` clause only dictates how the row number values are assigned, it guarantees nothing about the order ofg the final result set – Cristian Lupascu Oct 04 '13 at 07:51
  • I am struggling to comprehend this below, INSERT INTO Sales.MyOrders(orderid, custid, empid, orderdate) SELECT NEXT VALUE FOR Sales.SeqOrderIDs OVER(ORDER BY orderid), custid, empid, orderdate FROM Sales.Orders WHERE custid = 1; The book says the "OVER clause with an ORDER BY list to control the order in which the sequence values are assigned to the result rows" If I can get clarification that would be great – TheWommies Oct 08 '13 at 07:49