How do I sort the top 10 entries in descending order in mySQL?

Question

I have a table holding entries which can be sorted by rank. I want to get the top 10 entries (which is simple using SELECT * FROM table ORDER BY rank DESC), but then I want those entries in descending order, so the one with the lowest rank ends up at the top. How would I do this?

Answer 1

You should be able to do:

SELECT    * 
FROM      (SELECT * FROM `table` ORDER BY rank DESC LIMIT 10) dt
ORDER BY  dt.rank ASC;

I guess you have a table like this:

CREATE TABLE `table` (id int, rank int);
INSERT INTO `table` VALUES (1, 20), (2, 19), (3, 18), (4, 17), (5, 16), (6, 15),
                           (7, 14), (8, 13), (9, 12), (10, 11), (11, 10), 
                           (12, 9), (13, 8), (14, 7), (15, 6), (16, 5), (17, 4), 
                           (18, 3), (19, 2), (20, 1);

You would get a result like this:

+------+------+
| id   | rank |
+------+------+
|   10 |   11 |
|    9 |   12 |
|    8 |   13 |
|    7 |   14 |
|    6 |   15 |
|    5 |   16 |
|    4 |   17 |
|    3 |   18 |
|    2 |   19 |
|    1 |   20 |
+------+------+
10 rows in set (0.02 sec)

---

UPDATE:

@onik's solution returns the same result.

Answer 2

(SELECT * FROM table ORDER BY rank DESC LIMIT 10) ORDER BY rank ASC;

Is this what you're looking for?