Order by more than one column with mysql

Question

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

I can see at least 10 different orderings that give the same result as your sample. Please clarify with more data, that have same/different data in `profile_type` and/or `is_elite` columns. — ypercubeᵀᴹ, Mar 27 '12 at 12:32
Again edited question , please read all 3 are linked together — Duke, Mar 27 '12 at 12:36
Let me ask a couple of questions to help clarify... Is a Premium profile always elite? If not, please add a row to your example that illustrates the sorting when this is not the case. Second, is "fav_count" actually a count or is it a ranking? I'm not sure this matters, since "fav_count" seems to be your least important sort column. — JerseyMike, Mar 27 '12 at 13:37
@JerseyMike Yes fav_count is just a count of how many favorite votes for one driver — Duke, Mar 27 '12 at 16:17
@duke, and is a premium profile always elite? If not, please add a row to your example that indicates how that would be sorted. — JerseyMike, Mar 27 '12 at 16:28

score 5 · Answer 1 · answered Mar 27 '12 at 12:25

5

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

order by profile_type desc, fav_count desc

answered Mar 27 '12 at 12:25

Randy

16,480
1
37
55

score 4 · Accepted Answer · answered Mar 27 '12 at 12:28

4

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.

answered Mar 27 '12 at 12:28

JerseyMike

849
7
22

By the way, I am assuming that "is_elite" has more precedence than "profile_type", sort-wise. If that's not true, put "profile_type" first in the ORDER BY list and "is_elite" second. – JerseyMike Mar 27 '12 at 12:30
@duke, here is how to build your ORDER BY... #1: Pick the field that is most important (sorting-wise) and add it to the list. #2: If it should be sorted with the largest values first, add "DESC". #3: Go back to #1 until you are out of sort columns. If you follow those rules, you should get what you want. – JerseyMike Mar 27 '12 at 13:33

score 1 · Answer 3 · answered Mar 27 '12 at 12:25

1

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

answered Mar 27 '12 at 12:25

Manuel

10,153
5
41
60

score 0 · Answer 4 · answered Mar 27 '12 at 12:25

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.

Order by more than one column with mysql

4 Answers4

Linked