CODEWITHSUNDEEP
sqlsql-server
Nullsig
Nullsig

Reputation: 38

TOP 1 and ORDER BY not returning correct results

I have read the other topics on this but they don't seem to match my scenario. I have a query that is ordering the results by Entry Date ASC and then by Sort ASC.

Query Results

The results shown are correctly ordered, however when I change my query to only pull TOP 1 it returns the second result instead. I have no idea why or how this happens.

Upvotes: 1

Views: 188

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

If your query has the order by in the outermost select, then the results should be returned in that order. Period.

If the order by is anywhere else -- in a subquery or in a window frame specification -- then the results might look like they are ordered, but the ordering is not guaranteed.

My guess is that you don't have the explicit order by that the query needs to do what you intend.

Also, although not the case with your sample data, if the keys have the same value then they can appear in any order -- and in different positions when you run the query multiple times.

Upvotes: 1

Related Questions