CODEWITHSUNDEEP
mysql
Abid
Abid

Reputation: 74

mysql custom order by query on same table

I have data like and it have more columns not only below two:

id       |  s_type
1        |  ACTIVE
2        |  PENDING
3        |  UPDATE
4        |  ACTIVE
5        |  PENDING
6        |  UPDATE

I used order by ASC, DESC but i want to display data with custom order and i want to show it as my custom orders (not only asc or descending): want to see first all PENDING then all ACTIVE then all UPDATE s_type

id       |  s_type
1        |  PENDING
2        |  PENDING
3        |  ACTIVE
4        |  ACTIVE
5        |  UPDATE
6        |  UPDATE

How can i display with single query

Upvotes: 1

Views: 22

Answers (2)

Abid
Abid

Reputation: 74

i tried this and it works:

      SELECT * FROM mytable ORDER BY 
    s_type = 'UPDATE',
signal_status = 'ACTIVE',
signal_status = 'PENDING'

Upvotes: 0

Blank
Blank

Reputation: 12378

Try this:

SELECT *
FROM yourtable
ORDER BY field(s_type, 'PENDING', 'ACTIVE', 'UPDATE'), id

Or use CASE WHEN

SELECT *
FROM yourtable
ORDER BY CASE s_type WHEN 'PENDING' THEN 1
                     WHEN 'ACTIVE' THEN 2
                     WHEN 'UPDATE' THEN 3 END, id

Upvotes: 1

Related Questions