0

I need to use 2 order by in my procedure.

When I use only the second order by is working and not the first one.

What mistake am I doing?

Here is my procedure 

BEGIN

SELECT 
    sd.ShopID,
    @dist:= ( 6371 * acos( cos( radians(lat) ) * cos( radians( sd.Lat ) ) * cos( radians( sd.Lng ) - radians(lng) ) + sin( radians(lat) ) * sin( radians( sd.Lat ) ) ) ) AS distance 
    FROM 
        shop_details sd 
    WHERE 
        sd.Status = '1' 
    Order by 
        sd.Status Asc,
        distance Asc;
    END

(Don't care about the @dist formulae, as it will return the distance)

In the end I want to display the status as Ascending and distance as Ascending.

While I use 

Order by 
    sd.Status Asc,
    distance Asc

I got only distance as Asc and I got the sd.Status is not in ordered. I tried it vice versa but I got the same issue.

How can I do this without using a separate table like in this Question?

I tried this way.

Community
  • 1
Sulthan Allaudeen
  • 101
  • 4

1 Answers1

1

Your use of ORDER BY is correct. However, You need to know that it has to go in order by how you specified! So your query will first order sd.Status FIRST, then it will order by distance SECOND.

For example This is how you're expecting it to look (Just my random data I gave)

 sd.Status   distance
 ---------   --------
     A         231
     A         245
     A         657
     A         987
     B         123
     B         432
     D         465
     D         576
     S         555

Note how it is ORDERED.

Twister1002
  • 111
  • 3