Sql SELECT TOP 1

Question

I'm getting weird results from a table I'm selecting from. Consider the following table:

USERID    TICKETSASSIGNED

1         0  
100       0  
101       0  
102       0  
103       0  

Now, if I have the following sql as in:

SELECT TOP 1 USERID  
  FROM TABLEX  
  ORDER BY TICKETSASSIGNED

The result I would expect to get is "1" but most all the time I'm getting "100", the second record. Since TICKETSASSIGNED values are all "0", it randomly picks which one it thinks is TOP 1 since this is the field I'm ordering on. To get the correct value of "1" in this case, I had to also order on USERID.

Any ideas?

Thanks,

David W

Answer 1

You would need a second column in your ORDER BY clause in this case:

SELECT TOP 1 USERID  
FROM TABLEX  
ORDER BY TICKETSASSIGNED, USERID

Without this it's probably giving you the records ordered by TICKETASSIGNED, then by their physical location in the table, e.g. the order they were inserted in most cases.

Answer 2

If you are ordering only by TICKETSASSIGNED and they all have equal values, there is no reason any specific record should be returned as the top one. If you want to get the top one using USERID as the next field to order by, then yes, you should just add USERID to your order by clause.

Answer 3

The result I would expect to get is "1" but most all the time I'm getting "100", the second record. Since TICKETSASSINGED values are all "0", it randomally picks which one it thinks is TOP 1 since this is the field I'm ordering on. To get the corredct value of "1" in this case, I had to also order on USERID.

This is default behavior in all SQL - there's no order guarantee if there's no ORDER BY clause, and in this case you're not ordering by pertinent data so the database arbitrarily picks a row.

Use:

ORDER BY TICKETSASSIGNED, USERID    

Answer 4

If you check the documentation for TOP: TOP (Transact-SQL), specifically the first two paragraphs under Remarks, it explicitly states that TOP does not order the rows. As such, the order you've imposed yourself is the one used.

Unfortunately, your ordering is incomplete. It only happens to produce what you want sometimes.

The correct way is exactly what you've figured out, you need to order by USERID as well.