Does ORDER BY only work with columns?

Question

Why does SELECT * FROM TABLE ORDER BY RAND() Work? I thought ORDER BY only works for columns.

So what exactly does it mean to ORDER BY RAND() or ORDER BY SUM()?

Answer 1

Well, to put it short: You thought wrong.

Answer 2

You can order by nearly everything, functions (like RAND()), aggregations (like SUM()) and so on.

For example, the MySQL-Documentation states as Syntax:

 [ORDER BY {col_name | expr | position}

Or the Postgresql Documentation is even more explicit:

The optional ORDER BY clause has this general form:

ORDER BY expression [ ASC | DESC | USING operator ] [, ...]

expression can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary expression formed from input-column values.

For your second question:

• ORDER BY RAND() or ORDER BY RANDOM() does what it states: Your rows are shuffeled and you get them in a random order. So if you do a SELECT * FROM ... ORDER BY RAND() LIMIT 1 you select a (=one) random row out of your table.
• ORDER BY SUM(Column) only makes sense in combination with a GROUP BY statement.

Answer 3

ORDER will work with any value you can put in your results (but doesn't have to be one of the values in the results). This can be a column in any of the source tables or calculated using a function. For example, you could use ORDER UPPER(name) for a case-insensitive sort.

If you ORDER BY RAND() you're ordering by a random number generated for each row in the results, i.e. returning the rows in a random order. If you're ordering by SUM() you've probably got a GROUP BY in there too so you could order customers by total calculated invoice total for example.

Ideally you want to use a column from an index as this will be much faster.

Answer 4

Actually it depends on the implementation.The statement you mentioned is not SQL92-compatible but it may be accepted by an implementation.