Why is query not working on mariaDB?

Question

MySQL

Hello. There is a SQL query:

SELECT * FROM (SELECT * FROM `b_users` WHERE `b_id`=4 ORDER BY `id` DESC) AS t GROUP BY `user_id`

On the MySql 5.6 server, the query works correctly. But on MariaDB 10.1.34 sorting (DESC) does not work. It's as if the system doesn't define nested (SELECT) . Though separately this request everywhere works correctly.

SELECT * FROM `b_users` WHERE `b_id`=4 ORDER BY `id` DESC

Can you tell me what the compatibility issue might be?
Thanks in advance.

Answer 1

So, when grouping, the sorting of the inner query does not have to be preserved. And in MySQL 5.7.5 and higher with default settings, such a query will generally generate an error; in queries with grouping by standard, you can only query the fields included in GROUP BY or the results of aggregate functions.

Answer 2

Do without subquery just it

Answer 3

And what result do you consider correct: sorted by id in descending order? If so, then, unfortunately, the outer group by implicitly adds a sort by the group field, and I would advise adding the sort to the outer query.