CODEWITHSUNDEEP
mysqlsqlsql-order-by
Martin AJ
Martin AJ

Reputation: 6697

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?

Upvotes: 1

Views: 51

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

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.

Upvotes: 2

Related Questions