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?

Answer 1

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 2

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.