ltvie - 1 year ago 37

MySQL Question

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

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

- 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

Answer

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
```

Source (Stackoverflow)