Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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?
                What is your primary key? The data might actually be stored different from the results that are being displayed.
– Jerod Houghtelling
                Aug 10, 2010 at 21:32
  
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.
ORDER BY TICKETSASSIGNED, USERID    
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.
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.