Clever Geek Handbook

List of records by multiple criteria, paginated

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

    - INT (11) AUTO_INCREMENT
  • order

    - INT (11)
  • highlighted

    is INT (11) and only contains the values ​​"1" or "0"

I would like to sort these records in one query like this:

  • first display all lines that have highlighted

    = '1' ordered by order

    desc
  • followed by lines which highlighted

    = '0' ordered by id

    desc
  • the resulting list should be paginated ( limit 30, 10

    - as an example, this would be a page 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!

+3


source to share


2 answers


Try the following:



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;
+1


source


try below:



    select * from 
    (
    select * from tableA where highlighted = 1 Order By order desc    
    UNION     
    select * from tableA where highlighted = 0 Order By id desc
     )alias    LIMIT 30, 10
0


source






More articles:


All Articles