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.

closed as unclear what you're asking by Mat, RolandoMySQLDBA, Mark Storey-Smith, Paul White, RLF Nov 10 '14 at 16:40

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Sulthan, it's best to limit your code samples to the minimum that will demonstrate the problem you would like help with. Please edit your question and remove the parts of the query which are not needed. – Michael Green Sep 10 '14 at 11:05
  • @Michael Green Yes i have updated my question. – Sulthan Allaudeen Sep 10 '14 at 11:13
  • Show some sample data,I suspect a misunderstanding of how ORDER BY works. – Mihai Sep 10 '14 at 11:17
  • Your query filters on sd.Status = '1', so ordering on that column is pretty useless... there's only one value. If you still have this issue, please update your question is a realistic query and sample data (and output). – Mat Nov 7 '14 at 16:59
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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.