i have stores records in table , want sort them on basis of highest rating of store , has nearest distance location.
select rating, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lon ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) distance mystores sr order sr.rating desc ,distance asc it not giving me desired results
table mystores
id|rating|distance 66 5 55 55 4 56 99 3 60
i assume want closest store , want ignore other same rank bigger distance. group raiting allows minimal distance each raiting.
select m1.* mystores m1 join ( select m.raiting, min(m.distance) distance mystores m group m.raiting ) m2 on m2.raiting = m1.raiting , m2.distance = m1.distance order m1.raiting desc, m1.distance asc 