CTE top - order by clause is not always working

Question

I have the following T-SQL statement:

;WITH DataSource ([ColumnA]) AS
(
    SELECT TOP 100 [ColumnA] 
    FROM [dbo].[TEST] 
    WHERE [ColumnB] = 40 
    ORDER BY [ColumnC] DESC
)
SELECT [ColumnA]
      ,COUNT([ColumnA])
FROM DataSource
GROUP BY [ColumnA]

It is simply not returing the same result. I can not understand way, but some time the T-SQL statement in the CTE is not returning the correct values are because of this different results is produce, not each time, but sometimes (to be more accurate I am getting three different results).

If I excute only the following statement:

SELECT TOP 100 [ColumnA] 
FROM [dbo].[TEST] 
WHERE [ColumnB] = 40 
ORDER BY [ColumnC] DESC

I am getting the same results each time. And if I am using derived table there is no problems too:

SELECT [ColumnA]
      ,COUNT([ColumnA])
FROM
(
    SELECT TOP 100 [ColumnA] 
    FROM [dbo].[TEST] 
    WHERE [ColumnB] = 40 
    ORDER BY [ColumnC] DESC

) DataSource
GROUP BY [ColumnA]

Can anyone explaing why the CTE is not returning the correct results each time?

Is `ColumnC` unique? If not you will need to add a guaranteed unique column(s) to the `ORDER BY` to act as a tie breaker to ensure deterministic results. — Martin Smith, Feb 28 '14 at 14:39
In what way are the results wrong? Is it returning (up to) 100 rows where `ColumnB` equals 40 and the `ColumnC` values are as low as possible? Because that's all it guarantees to do. — Damien_The_Unbeliever, Feb 28 '14 at 14:46
@Damien_The_Unbeliever Yes, you right - what the hell I was thinking about... — gotqn, Feb 28 '14 at 14:47

score 0 · Answer 1 · answered Mar 03 '14 at 16:56

0

An ORDER BY is not allowed inside the CTE construct. Please see the remarks section here: MSDN. Provide the order by as part of the CTE_QUERY_DEFINITION. I am surprised SQL server even let you run it.

answered Mar 03 '14 at 16:56

Matt

1,441
1
15
29

From your link: `ORDER BY (except when a TOP clause is specified)` – gotqn Mar 03 '14 at 17:03
Wow I completely missed your TOP statement. My apologies. Did you see this? http://stackoverflow.com/questions/3924940/sql-cte-and-order-by-affecting-result-set – Matt Mar 03 '14 at 19:29

score 0 · Accepted Answer · answered Mar 24 '14 at 21:26

As Martin Smith pointed in his comment:

Is ColumnC unique? If not you will need to add a guaranteed unique column(s) to the ORDER BY to act as a tie breaker to ensure deterministic results.

There is nothing wrong with the CTE itself - the issue is I am not sorting by unique column.

CTE top - order by clause is not always working

2 Answers2