CODEWITHSUNDEEP
mysql
steven
steven

Reputation: 33

how to sort results by specific values in mysql

We have a DB called transaction. It has user_id, date, value and so on. I use pagination in my query also. I have thousands of record in my table which has user_id equal to 2 or other value. put the user_id = 2 at the very last page. I want to sort the result like this:

sort the results by date but if the user_id= 2 , put all results associated with the user_id= 2 at the end. to be more clear, I show you what I want in the below.

-------------------------------------
| ID | user_id |    date    | ......
-------------------------------------
| 1  | 10      | 2018-10-20 |
-------------------------------------
| 2  | 11      | 2018-10-21 |
-------------------------------------
| 3  | 2       | 2018-10-22 |
-------------------------------------
| 4  | 2       | 2018-10-23 |

the results have to be like this:

first: ID = 2, second: ID = 1, third: ID = 4, last: ID = 3

tip *: I use field function but unfortunately in vain.

ORDER BY FIELD(user_id, 2) DESC, date DESC

Upvotes: 1

Views: 34

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520928

You may try using a CASE expression in your ORDER BY clause:

SELECT *
FROM yourTable
ORDER BY
    CASE WHEN user_id = 2 THEN 1 ELSE 0 END,
    date DESC;

I'm not sure if you want each group sorted by date ascending or descending. If you want ascending date order, then remove the DESC keyword at the end of my query.

Upvotes: 1

Related Questions