Search code examples
mysqlsql-order-bylimit

Get another order after limit

Imagine I've a table 'users' with two fields: 'age' and 'name'. I want to retrieve the top ten older users and then I want this list of ten sorted by name.

Is it possible to do it with MySQL?

I've tried this: (doesn't work)

SELECT * FROM users order by age, name limit 10
Solution

  • Use a subselect:

    SELECT * FROM
(
    SELECT *
    FROM users
    ORDER BY age DESC
    LIMIT 10
) AS T1
ORDER BY name

    The inner select finds the 10 rows you want to return, and the outer select puts them in the correct order.