CODEWITHSUNDEEP
sql-servercommon-table-expression
user499054
user499054

Reputation:

Will ROW_NUMBER() always order the outer result?

I'm using a CTE to rank book authors based on their sales rank. I understand that ROW_NUMBER() can take in an ORDER BY to determine the returned row number based on it.

My query looks like this:

WITH cte (idWork, author, rn)
AS
(
    SELECT
        idWork,
        authorName,
        ROW_NUMBER() OVER (PARTITION BY idWork ORDER BY authorSalesRank)
    FROM dbo.Works_Authors_Works
    INNER JOIN Authors ON Authors.idAuthor = Works_Authors_Works.idAuthor
)
SELECT TOP 100 * FROM cte

I noticed that the the results look like this, which is as expected:

+---------+----------------+------+
| idWork  | author         | rn   |
+---------+----------------+------+
| 1       | test1          | 1    |
| 1       | test2          | 2    |
| 1       | test3          | 3    |
| 2       | beep1          | 1    |
| 2       | beep2          | 2    |
| 3       | nobody         | 1    |
+---------+----------------+------+

I have two questions with this CTE:

In the execution plan, it has an input Sort on idWork, authorSalesRank. Do these imply that the outer result is sorted on this too?

execution plan execution plan - sort details

Upvotes: 1

Views: 59

Answers (1)

Bohemian
Bohemian

Reputation: 425198

You should explicitly order it.

WITH cte (idWork, author, rn) AS (
    SELECT
        idWork,
        authorName,
        ROW_NUMBER() OVER (PARTITION BY idWork ORDER BY authorSalesRank) AS rn
    FROM dbo.Works_Authors_Works
    INNER JOIN Authors ON Authors.idAuthor = Works_Authors_Works.idAuthor
)
SELECT TOP 100 *
FROM cte
order by idWork, rn

SQL (all databases) do not guarantee order if no order is specified, even if it seems like it "always works", you should not rely on that anecdotal evidence.

Upvotes: 3

Related Questions