Using 2 order by in procedure [closed]

Question

Closed. This question needs details or clarity. It is not currently accepting answers.

---

Want to improve this question? As written, this question is lacking some of the information it needs to be answered. If the author adds details in comments, consider editing them into the question. Once there's sufficient detail to answer, vote to reopen the question.

Closed 10 years ago.

Improve this question

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.

• Michael Green: 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.
• Sulthan Allaudeen: @Michael Green Yes i have updated my question.
• Mihai: Show some sample data,I suspect a misunderstanding of how ORDER BY works.
• Mat: 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).

Answer 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.