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).

Answer 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

Answer 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..