Pk19000 Pk19000 - 1 month ago 7
MySQL Question

Order by different field based on criteria

I have two series of posts. Each post has some percentage of progress (

project_percent
) and a end time (
end_date
). I want to first show posts with
end_date
in the future ('DESC') and after that I want to show project with
end_date
in the past order by percent. How can I do this with just one query?

(If date is not pass percent not important and just date is important but if date passed I wanna sort by percent)

select * from posts ...


--------------------------------------
| post | end_date | project_percent|
| P1 | 28-10-2016| 50 |
| P2 | 26-10-2016| 40 |
| P2 | 30-10-2016| 20 |
| P3 | 24-10-2016| 75 |


* today is
27-10-2016


I want the output to be like this:

--------------------------------------
| post | end_date | project_percent|
| P2 | 30-10-2016| 20 |
| P1 | 28-10-2016| 50 |
| P3 | 24-10-2016| 75 |
| P2 | 26-10-2016| 40 |


*The first two sorted by date and the second two sorted by persent

Answer

Give this a try:

select *, end_date < now() as flag from posts
order by flag
, case when flag = 0 then end_date end desc
, case when flag = 1 then project_percent end desc;

Could make it simpler by using

select * from posts
order by 
    case when end_date < now() = 0 then end_date end desc
    , case when end_date < now() = 1 then project_percent end desc;

but the first version is nicer for debugging.