ltvie ltvie - 2 years ago 79
MySQL Question

sql statement order by with condition always failed?

I want to show all row with custom order by, in part of first rows are which have the same data as the 2 days ago till current date (use 2016-06-18 as current date), after that please custom the rows based on the smallest number of dist,
I use this sql to retrieve data but always failed.

SELECT pwaktuserver as date,
acos(cos(-7.47353794753 * (PI()/180)) *
cos(112.343534533 * (PI()/180)) *
cos(plat * (PI()/180)) *
cos(plong * (PI()/180))
cos(-7.47353794753 * (PI()/180)) *
sin(112.343534533 * (PI()/180)) *
cos(plat * (PI()/180)) *
sin(plong * (PI()/180))
sin(-7.47353794753 * (PI()/180)) *
sin(plat * (PI()/180))
) * 3959 as Dist
FROM tbpos INNER JOIN tbanggota ON tbanggota.id_a = tbpos.id_a WHERE stsTampil <> '2'
GROUP BY tbpos.posid
ORDER BY case when date(pwaktuserver)>=curdate()-2 then Dist*(-1) else Dist end

the result of sql statement above
enter image description here

NOTE : 1. yellow box is group of row that consist of date with 2 days ago

  1. Blue box is group of row that consist of distance with row sort asc.

sort of data return corect value yellow box as first part and blue box as second, but my problem is in yellow box the date not ordered DESC, i want to make yellow box ordered desc, how i achieve my goal ? thanks

Answer Source

Just add another condition.

  ORDER BY CASE WHEN date(pwaktuserver)>=curdate()-2 THEN date(pwaktuserver)
                                                     ELSE null
           END DESC,  
           CASE WHEN date(pwaktuserver)>=curdate()-2 THEN Dist*(-1) 
                                                     ELSE Dist 
           END ASC -- OR DESC
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download