Dantom Dantom - 8 days ago 6
SQL Question

Why does SELECT TOP 1 . . . ORDER BY return the second row in the table?

When I select all rows with the query:

SELECT *
FROM AFT_Contacts
ORDER BY Tries


I see the result:

ID Name Area Phone Status Tries
117970 Adam One 1111111111 New 0
117971 Brian Two 2222222222 New 0
117972 Colin Three 3333333333 New 0
117973 David Four 4444444444 New 0
117974 Edward Five 5555555555 New 0
117975 Frank Six 6666666666 New 0


But the Query:

SELECT TOP 1 * FROM AFT_Contacts ORDER BY Tries


Returns:

ID Name Area Phone Status Tries
117971 Brian Two 2222222222 New 0


Why does it not return Adam's details as they are first in the table?

Answer

In SQL tables have no inherent order. The ORDER BY you give is not distinct over all records, in fact it's the same over all records. So the order in which results are returned is still not deterministic and unpredictable. And therefor the top 1 returns an unpredictable row.

You say "Adam's details are first in the table", this is simply not true; records in a table are stored without any order. If you select without an order by or (as in your case) the order by is not deterministic the returned order is arbitrary.

Comments