Home Mysql: Reorder results based on multiple column values then date
Reply: 0

Mysql: Reorder results based on multiple column values then date

user1224
1#
user1224 Published in August 15, 2018, 11:15 am

This is mytable:

id | state   | orderid | name  | date
1  | Hold    | 121212  | Mike  | 2018-04-11
2  | Closed  | 121213  | Homer | 2018-04-12
3  | Open    | 121214  | Mike  | 2018-04-13
4  | Hold    | 121215  | Bart  | 2018-04-14
5  | Open    | 121216  | Lisa  | 2018-04-15
6  | Closed  | 121217  | Bart  | 2018-04-16
7  | Closed  | 121218  | Homer | 2018-04-17

I'm trying to order by state (open, hold, close), then date (ASC), if the state is "closed", then date (DESC). So a result like this.

id | state   | orderid | name  | date
3  | Open    | 121214  | Mike  | 2018-04-13
5  | Open    | 121216  | Lisa  | 2018-04-15
1  | Hold    | 121212  | Mike  | 2018-04-11
4  | Hold    | 121215  | Bart  | 2018-04-14
7  | Closed  | 121218  | Homer | 2018-04-17
6  | Closed  | 121217  | Bart  | 2018-04-16
2  | Closed  | 121213  | Homer | 2018-04-12

This is the query I have now:

SELECT * FROM mytable ORDER BY FIELD(state,'Open','Hold','Closed') ASC, o_date DESC

Have attempted to add the examples given here: Can you add an if statement in ORDER BY? but I'm not getting the results I want.

Any ideas?

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.31494 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO