CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Paul van Valkenburgh
Paul van Valkenburgh

Reputation: 75

Order By Clause seems to be not working

I have a full-text search that utilizes TOP and Order By. I've used this same query in another database and my order always stays the same. For some reason on a different database, the order always seems to change a little. Not a lot, but just a little. Here is what it looks like

SELECT TOP 20 * FROM 
(
SELECT DISTINCT CandidateID, [Resume], FirstName, MiddleName, LastName, 
       ROW_NUMBER() OVER (ORDER BY CreatedDate DESC) AS num

       FROM dbo.tblCandidates  WHERE (CONTAINS(([resume]), @SearchItem))

) AS a 
WHERE num > @start

Can anyone think of a reason that the Order By clause wouldn't always work in this case?

Thanks!

Upvotes: 2

Views: 186

Answers (1)

anon
anon

Reputation:

You don't HAVE an ORDER BY clause (the one inside the OVER() clause is used to determine ROW_NUMBER() and does not usually have any influence whatsoever of the order of the output). If you've observed a specific order from this query on other systems, it has been purely coincidental and should not be relied upon. If you want a specific order, you need to add ORDER BY to the outer query, no matter what you think it should do or what you have observed elsewhere.

SELECT TOP 20 * FROM 
(
SELECT DISTINCT CandidateID, [Resume], FirstName, MiddleName, LastName, 
       ROW_NUMBER() OVER (ORDER BY CreatedDate DESC) AS num

       FROM dbo.tblCandidates  WHERE (CONTAINS(([resume]), @SearchItem))

) AS a 
WHERE num > @start
ORDER BY num;

Upvotes: 4

Related Questions