Stack Stack - 6 months ago 10
MySQL Question

Do I need inner ORDER BY when there is an outer ORDER BY?

Here is my query:

( SELECT id, table_code, seen, date_time FROM events
WHERE author_id = ? AND seen IS NULL

) UNION
( SELECT id, table_code, seen, date_time FROM events
WHERE author_id = ? AND seen IS NOT NULL
LIMIT 2

) UNION
( SELECT id, table_code, seen, date_time FROM events
WHERE author_id = ?
ORDER BY (seen IS NULL) desc, date_time desc -- inner ORDER BY
LIMIT 15
)
ORDER BY (seen IS NULL) desc, date_time desc; -- outer ORDER BY


As you see there is an outer
ORDER BY
and also one of those subqueries has its own
ORDER BY
. I believe that
ORDER BY
in subquery is useless because final result will be sorted by that outer one. Am I right? Or that inner
ORDER BY
has any effect on the sorting?




Also my second question about query above: in reality I just need
id
and
table_code
. I've selected
seen
and
date_time
just for that outer
ORDER BY
, Can I do that better?

Answer

You need the inner order by when you have a limit in the query. So, the third subquery is choosing 15 rows based on the order by.

In general, when you have limit, you should be using order by. This is particularly true if you are learning databases. You might seem to get the right answer -- and then be very surprised when it doesn't work at some later point in time. Just because something seems to work doesn't mean that it is guaranteed to work.

The outer order by just sorts all the rows returned by the subqueries.

Comments