samedi 18 juin 2016

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

Aucun commentaire:

Enregistrer un commentaire