Multiple Order By in MySql Query

Question

I am developing a voting system. I am facing issue in ordering. Basically i want to get top ranking and maximum nominee first. I used "totalUserVoted" and "totalRating" in Descending order on both condition but my query ordering "totalUserVoted".

I am expecting the result in this order.

Here is my sql query.

SELECT
  (SELECT (((SUM(`design`)*4)+(SUM(`usability`)*3)+(SUM(`creativity`)*2)+(SUM(`content`))*1))/ count(`nominee_id`) / 10
   FROM `sk_award_nominee_rating`
   WHERE `sk_award_nominee_rating`.`nominee_id`=`sk_award_nominee`.`nominee_id`) AS totalRating,
  (SELECT count(`nominee_id`)
   FROM `sk_award_nominee_rating`
   WHERE `sk_award_nominee_rating`.`nominee_id`=`sk_award_nominee`.`nominee_id`) AS totalUserVoted,
       `sk_award_nominee`.*,
       `sk_user`.`username`,
       `sk_user`.`email`,
       `sk_user_profile`.`f_name`,
       `sk_user_profile`.`m_name`,
       `sk_user_profile`.`l_name`,
       `sk_user_profile`.`address`
FROM `sk_award_nominee`
LEFT JOIN `sk_user` ON `sk_user`.`user_id`=`sk_award_nominee`.`user_id`
LEFT JOIN `sk_user_profile` ON `sk_award_nominee`.`user_id`=`sk_user_profile`.`user_id`
WHERE `sk_award_nominee`.`status` = 1
  AND DATE(approval_date) = '2016-02-22'
ORDER BY `totalUserVoted` DESC,
         `totalRating` DESC

Can you explain the ordering that you're trying to get? Do you want it ordered by `totalUserVoted + totalRating`? — Tom H, Feb 29 '16 at 06:18
Basically i want in the top that nominee have highest rating with maximum number of user votes. — Manoj Kumar, Feb 29 '16 at 06:23
How are rating and number of users combined in order to get the expected result? The user having the highest rating is 3rd in the expected result. How is that so? — Giorgos Betsos, Feb 29 '16 at 06:36
@GiorgosBetsos Yes you are right but i want to get result on both conditions. A nominee having highest rating with maximum number of votes. For e.g. If a Nominee have AVG 7.0 rating with 1 vote, another user have 3.5 rating with 8 users and another one have 6.8 rating with 6 votes. So 6.8 rated user in the top and then 3.5 with 8 vote user and after that 7.0 with 1 vote. I want to ordering like this. — Manoj Kumar, Feb 29 '16 at 06:42
@GiorgosBetsos Thanks it will worked :). I get the result i that i am expecting. — Manoj Kumar, Feb 29 '16 at 06:53

score 0 · Answer 1 · edited May 23 '17 at 11:53

0

Something like this ?

ORDER BY totalUserVoted DESC, totalRating DESC

PHP MySQL Order by Two Columns

edited May 23 '17 at 11:53

Community

1
1

answered Feb 29 '16 at 06:20

Aamir

326
2
8

1

Please read my query firstly. I used the same thing but not getting expected result. Please take a look again on the question. – Manoj Kumar Feb 29 '16 at 06:24

Multiple Order By in MySql Query

1 Answers1