List entries on multiple criterias, paginated

Question

I need your help in the following situation:

I have a table that looks like this

----------------------------
| id | order | highlighted |
----------------------------
|  1 |   5   |      1      |
----------------------------
|  2 |   7   |      1      |
----------------------------
|  3 |   0   |      0      |
----------------------------
|  4 |   0   |      0      |
----------------------------
|  5 |   9   |      1      |
----------------------------
|        *and so on*       |
----------------------------

• id is INT(11) AUTO_INCREMENT
• order is INT(11)
• highlighted is INT(11) and contains only '1' or '0' values

I would like to sort those entries, in one query, like this:

• first display all rows that have highlighted = '1' ordered by order desc
• following are the rows that have highlighted = '0' ordered by id desc
• the obtained list should be paginated (limit 30, 10 - as an example, it would be Page 3 with 10 items per page)

The result should be:

----------------------------
| id | order | highlighted |
----------------------------
|  5 |   9   |      1      |
----------------------------
|  2 |   7   |      1      |
----------------------------
|  1 |   5   |      1      |
----------------------------
|  4 |   0   |      0      |
----------------------------
|  3 |   0   |      0      |
----------------------------
|        *and so on*       |
----------------------------

Any ideas? Thank you in advance for any suggestions!

Answer 1

Try this:

SELECT a.id, a.order, a.highlighted 
FROM tableA a
ORDER BY a.highlighted DESC, 
        (CASE WHEN a.highlighted = 1 THEN a.order ELSE a.id END) DESC
LIMIT 30, 10;