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

Question

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 1

In relational databases 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.