0

I have this query.

SELECT * FROM (SELECT * FROM private_messages ORDER BY id DESC) a

The "DESC" works on my local server but doesn't work on another server. However if i just write it like this:

SELECT * FROM private_messages ORDER BY id DESC

it works on both servers. What would cause this?

babyleans
  • 23
  • 2
  • 9

1 Answers1

0

This is going to be too long for a comment..

It's not a bug. It's documented here:

As of MySQL 5.7.6, the optimizer handles propagation of an ORDER BY clause in a derived table or view reference to the outer query block by propagating the ORDER BY clause if the following conditions apply: The outer query is not grouped or aggregated; does not specify DISTINCT, HAVING, or ORDER BY; and has this derived table or view reference as the only source in the FROM clause. Otherwise, the optimizer ignores the ORDER BY clause. Before MySQL 5.7.6, the optimizer always propagated ORDER BY, even if it was irrelevant or resulted in an invalid query.

However it's not the case for your query. So i guess your second server is running MariaDB which seams to ingnore any ORDER BY in a subquery without LIMIT

A "table" (and subquery in the FROM clause too) is - according to the SQL standard - an unordered set of rows. Rows in a table (or in a subquery in the FROM clause) do not come in any specific order. That's why the optimizer can ignore the ORDER BY clause that you have specified. In fact, SQL standard does not even allow the ORDER BY clause to appear in this subquery (we allow it, because ORDER BY ... LIMIT ... changes the result, the set of rows, not only their order).

You need to treat the subquery in the FROM clause, as a set of rows in some unspecified and undefined order, and put the ORDER BY on the top-level SELECT.

Why is ORDER BY in a FROM Subquery Ignored?

So best you can do is just to move the ORDER BY clause to the outer query. Or don't use a subquery at all.

Paul Spiegel
  • 30,925
  • 5
  • 44
  • 53
  • Thanks. But the query is part of a larger more complex query. And yes, it's MariaDB. I think i'll just use the `LIMIT`. – babyleans Feb 06 '17 at 00:10
  • @babyleans Are you trying to calculate something like row number or rank? – Paul Spiegel Feb 06 '17 at 00:11
  • I'm trying to `order by` and `group by` at the same time. – babyleans Feb 06 '17 at 00:35
  • @babyleans this as an outdated "trick" and might not work for other reasons in future versions even with LIMIT. If you want to select the "most recent row per group" - there are other ways which are usually also faster (like [here](http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1)). – Paul Spiegel Feb 06 '17 at 00:52
  • Thank you. i used self join. I finally got it to work! – babyleans Feb 06 '17 at 16:34