CODEWITHSUNDEEP
phpmysql
Duke
Duke

Reputation: 36970

Order by more than one column with mysql

The below shown is my query I need to order by with these three columns . This is for complex search listing. If I used one of them ,it works perfectly . But handling more than one ,it does not order properly.

EDIT

Actually I needed ,premium records must be at first positions along with more favorites , and also elite customers have more priority . These all works conditionally

1) More favorites will come at first

2) But premium will be considered

3) If basic will have 5 favorites ,it wont push to top ,basic having small priority

4) As well elite have some good priority 

SELECT  
   `Driver`.`id` ,  
   `Driver`.`login_id` ,  
   `Login`.`profile_type`, 
   .................
ORDER BY  `fav_count`, 
   FIELD(  `Login`.`profile_type` ,  "premium",  "basic" ) ,  
   `Driver`.`is_elite` ASC

Sample result I expected

is_elite|       profile_type  | fav_count
________|_____________________|____________
1       |        premium      |   4      
1       |        premium      |   3 
1       |        premium      |   2 
1       |        premium      |   1 
0       |        basic        |   5 
0       |        basic        |   0

Please advise me . Thanks

Upvotes: 1

Views: 6915

Answers (4)

JerseyMike
JerseyMike

Reputation: 899

If you want the output you listed, you need to reorder the values in the ORDER BY:

ORDER BY `Driver`.`is_elite` DESC, FIELD(  `Login`.`profile_type` ,  "premium",  "basic" ) DESC, `fav_count` DESC

Note: You also need to have descending (DESC) on all of your sort columns. Each of them is being sorted largest to smallest.

Upvotes: 4

Randy
Randy

Reputation: 16677

unsure of your question, but this gives the order you show in the example.

order by profile_type desc, fav_count desc

Upvotes: 5

Nick
Nick

Reputation: 6346

From the sample result you expected it looks like you wanted to use:

ORDER BY  FIELD(  `Login`.`profile_type` ,  "premium",  "basic" ), `fav_count` DESC,  `Driver`.`is_elite` ASC

But this is speculative at best, your question wasn't worded very well.

Upvotes: 0

Manuel
Manuel

Reputation: 10303

Make the ORDER BY something like this:

ORDER BY `table`.`is_elite`, `table`.`profile_type`, `table`.`fav_count` DESC

This will order in the way that you expect (as far as i can see from the code you have given).

Upvotes: 1

Related Questions