SQL CTE and ORDER BY affecting result set

Question

I've pasted a very simplified version of my SQL query below. The problem that I'm running into is that the ORDER BY statement is affecting the select results of my CTE. I haven't been able to understand why this is, my original thinking was that within the CTE, I execute some SELECT statement, then the ORDER BY should work on THOSE results.

Unfortunately the behavior that I'm seeing is that my inner SELECT statement is being affected by the order by, giving me 'items' that are not in the TOP 10.

Here is an example of data: (Indexed in reverse order by ID)

ID,   Date
9600  2010-10-12
9599  2010-09-08
9598  2010-08-31
9597  2010-08-31
9596  2010-08-30
9595  2010-08-11
9594  2010-08-06
9593  2010-08-05
9592  2010-08-02
....
9573  2010-08-10
....
8174  2010-08-05
....
38    2029-12-20

---

My basic query:

;with results as(
select TOP 10 ID, Date
from dbo.items
)
SELECT ID
FROM results

query returns:

ID,   Date
9600  2010-10-12
9599  2010-09-08
9598  2010-08-31
9597  2010-08-31
9596  2010-08-30
9595  2010-08-11
9594  2010-08-06
9593  2010-08-05
9592  2010-08-02

---

My query with the ORDER BY

;with results as(
select TOP 10 ID, Date
from dbo.items
)
SELECT ID
FROM results
ORDER BY Date DESC

query returns:

ID,   Date
38    2029-12-20
9600  2010-10-12
9599  2010-09-08
9598  2010-08-31
9597  2010-08-31
9596  2010-08-30
9595  2010-08-11
9573  2010-08-10
9594  2010-08-06
8174  2010-08-05

Can anyone explain why the first query will only return IDs that are in the top 10 of the table, and the second query returns the top 10 of the entire table (after the sorting is applied).

Solution 1

When you use SELECT TOP n you must supply an ORDER BY if you want deterministic behaviour otherwise the server is free to return any 10 rows it feels like. The behaviour you are seeing is perfectly valid.

To solve the problem, specify an ORDER BY inside the CTE:

WITH results AS
(
    SELECT TOP 10 ID, Date
    FROM dbo.items
    ORDER BY ID DESC 
)
SELECT ID
FROM results
ORDER BY Date

Solution 2

I think you can add new column like

SELECT ROW_NUMBER() OVER(ORDER BY <ColumnName>;) AS RowNo

and then all your columns.. this would help you to query using the CTE anchor... using between, where etc clauses..

Comments

[Brett]

I guess I was wanting it to return the top 10 rows from the table, and then order by those top 10 rows, not order the entire table, and choose the top 10 from that set... where is my mis-understanding?

[Mark Byers]

@Brett - You need to tell the database what you mean by "the top 10 rows from the table". Top 10 according to what metric? Perhaps sorted by date?

[Brett]

So are you saying I need to have an order by within the CTE? So: ;with results as( SELECT TOP 10 ID, PostedDate From items ORDER BY ID desc)?....My confusion stems from why an ORDER BY outside of the CTE is affecting the CTE results?

[Mark Byers]

@Brett: So are you saying I need to have an order by within the CTE? Yes. Otherwise you have absolutely no control over what 10 rows you will get back. It could be any 10 rows from the table. The fact that you get the rows you want sometimes is coincidence, and as you can see a small change to the query like adding an ORDER BY breaks your query. Similarly if you add a WHERE clause like WHERE x > 0 you could get another different set of 10 rows even if all rows have x > 0.

[Phil Sandler]

As Mark said, your ORDER BY outside the CTE is not affecting the results, the lack of an ORDER BY inside the CTE is affecting the results.