CTE top - order by clause is not always working

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?

Answers


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.


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.


Need Your Help

Avoid Literals In If Condition SonarQube error

java object sonarqube

I am getting error like Avoid Literals In If Condition in sonarqube , and unable to find the proper solution to it.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.