Search code examples
mysqlsqlsql-order-byderby

Why are my SQL queries yielding a different order?

I have a table like this:

Name     Seats
101      60
102      40
202      100
201      20

None of the data can be null, so each name of a room will have an attributed number of seats to it. Now here's my problem:

When I run this SQL Query:

select name from rooms

I get this output:

Name
101      
102      
201      
202

When I run this SQL Query:

select seats from rooms

I get this output:

Name
60
40
100
20

Notice in my first query select name from rooms, the order of the values in the tables changes, while in my second query, when I'm going for the seats, the order remains enact. Why is this the case?

How can I fix my SQL query so it doesn't reorder the names of my rooms?

I'd like it to maintain order and output this:

Name    
101      
102      
202      
201
Solution

  • SQL tables represent unordered sets. SQL results sets are unordered, unless there is an explicit ORDER BY corresponding to the outermost SELECT.

    Period.

    If you don't have an ORDER BY, you should have no expectations on the ordering of the results. The ordering can change from one run to another.

    And, even if you have an ORDER BY, if you have ties (i.e. keys with the same values), then they can be ordered arbitrarily from one run to the next.