MySQL ORDER BY multiple column ASC and DESC

Question

I have 2 MYSQL tables, users and scores. Detail:

users table:

enter image description here

scores table:

enter image description here

My intention is get 20 users list that have point field sort DESC (descending) combine avg_time field sort ASC (ascending). I use the query:

SELECT users.username, scores.point, scores.avg_time
FROM scores, users
WHERE scores.user_id = users.id
GROUP BY users.username
ORDER BY scores.point DESC, scores.avg_time
LIMIT 0, 20

The result is:

enter image description here

The result is wrong because the first line is exactly point = 100 and avg_time = 60.

My desired result is:

username    point    avg_time
demo123      100        60
demo123456   100       100
demo         90        120

I tried many times with different queries but the result is still wrong. Could you give me some solutions?

As namxee hinted, you have multiple rows per user. Do you want each users average of the "avg_time" and "point" columns? and use THAT result as the ordering criteria? If not, please show a sample result of what you ARE expecting. — DRapp, Dec 04 '12 at 03:38
@DRapp: I don't want each users average of the "avg_time" and "point" columns. I just want the "point" field to sort DESC (descending) and combine "avg_time" field sort ASC (ascending). I have edited my desired result. Sorry for my bad English. — Tan Viet, Dec 04 '12 at 07:35
Possible duplicate of [PHP MySQL Order by Two Columns](http://stackoverflow.com/questions/514943/php-mysql-order-by-two-columns) — Jim Fell, Jun 13 '16 at 19:39
Note to future readers: This question is not a good example of post formatting. Do not post images of what can be text / formatted text. [See here](https://meta.stackoverflow.com/a/285557/11107541) for why. See also: ["how to format a table in a post"](https://meta.stackoverflow.com/q/277716/11107541). — starball, Dec 22 '22 at 05:06

score 30 · Accepted Answer · answered Dec 04 '12 at 11:39

Ok, I THINK I understand what you want now, and let me clarify to confirm before the query. You want 1 record for each user. For each user, you want their BEST POINTS score record. Of the best points per user, you want the one with the best average time. Once you have all users "best" values, you want the final results sorted with best points first... Almost like ranking of a competition.

So now the query. If the above statement is accurate, you need to start with getting the best point/average time per person and assigning a "Rank" to that entry. This is easily done using MySQL @ variables. Then, just include a HAVING clause to only keep those records ranked 1 for each person. Finally apply the order by of best points and shortest average time.

select
      U.UserName,
      PreSortedPerUser.Point,
      PreSortedPerUser.Avg_Time,
      @UserRank := if( @lastUserID = PreSortedPerUser.User_ID, @UserRank +1, 1 ) FinalRank,
      @lastUserID := PreSortedPerUser.User_ID
   from
      ( select
              S.user_id,
              S.point,
              S.avg_time
           from
              Scores S
           order by
              S.user_id,
              S.point DESC,
              S.Avg_Time ) PreSortedPerUser
         JOIN Users U
            on PreSortedPerUser.user_ID = U.ID,
      ( select @lastUserID := 0,
               @UserRank := 0 ) sqlvars 
   having
      FinalRank = 1
   order by
      Point Desc,
      Avg_Time

Results as handled by SQLFiddle

Note, due to the inline @variables needed to get the answer, there are the two extra columns at the end of each row. These are just "left-over" and can be ignored in any actual output presentation you are trying to do... OR, you can wrap the entire thing above one more level to just get the few columns you want like

select 
      PQ.UserName,
      PQ.Point,
      PQ.Avg_Time
   from
      ( entire query above pasted here ) as PQ

Actually you are very intelligent developer. I am quite difficult to explain my intention because of my little English vocabulary. That's that answer I need. Thanks for explaining clearly in your answer! :) — Tan Viet, Dec 05 '12 at 02:23
@TanViet, more than happy to help, and use this as a bit of a guide for future. Having sample data and what you want out is a great help. Also, keep clear the underlying What am I looking for before getting the final (in this case), only the best record per each person. As for your English, you did fine. You got your idea across and your solution, so dont worry about it. — DRapp, Dec 05 '12 at 02:36

namxee · Answer 2 · 2012-12-04T02:33:22.543

3

i think u miss understand about table relation..

users : scores = 1 : *

just join is not a solution.

is this your intention?

SELECT users.username, avg(scores.point), avg(scores.avg_time)
FROM scores, users
WHERE scores.user_id = users.id
GROUP BY users.username
ORDER BY avg(scores.point) DESC, avg(scores.avg_time)
LIMIT 0, 20

(this query to get each users average point and average avg_time by desc point, asc )avg_time

if you want to get each scores ranking? use left outer join

SELECT users.username, scores.point, scores.avg_time
FROM scores left outer join users on scores.user_id = users.id
ORDER BY scores.point DESC, scores.avg_time
LIMIT 0, 20

edited Dec 04 '12 at 02:33

answered Dec 04 '12 at 01:56

namxee

233
1
9

Please re-write using explicit `JOIN` syntax. – Kermit Dec 04 '12 at 02:03
@namxee: I understand about table relation but I don't understand why you use avg() function in the query. Can you explain me? – Tan Viet Dec 04 '12 at 02:19
@TanViet : there are multiple records of same user_id in scores table. so if you just JOIN the two tables. user_id is not an unique record. anyway i thought u intend to get average of point and avg_time of each user. – namxee Dec 04 '12 at 02:24
1

I've heard of *Miss Understood*. I think she's a friend of *Private Parts* – Bohemian Dec 04 '12 at 07:34
@namxee: Thanks! I don't want each users average of the "avg_time" and "point" columns. I just want the "point" field to sort DESC (descending) and combine "avg_time" field sort ASC (ascending). – Tan Viet Dec 04 '12 at 07:36

score 1 · Answer 3 · answered Jul 02 '14 at 15:19

@DRapp is a genius. I never understood how he coded his SQL,so I tried coding it in my own understanding.


    SELECT 
      f.username,
      f.point,
      f.avg_time
    FROM
      (
      SELECT
        userscores.username,
        userscores.point,
        userscores.avg_time
      FROM
        (
        SELECT
          users.username,
          scores.point,
          scores.avg_time
        FROM
          scores
        JOIN users
        ON scores.user_id = users.id
        ORDER BY scores.point DESC
        ) userscores
      ORDER BY
        point DESC,
        avg_time
      ) f
    GROUP BY f.username
    ORDER BY point DESC

It yields the same result by using GROUP BY instead of the user @variables.

babaoqi · Answer 4 · 2012-12-04T08:57:28.410

0

group by default order by pk id，so the result
username point avg_time
demo123 100 90 ---> id = 4
demo123456 100 100 ---> id = 7
demo 90 120 ---> id = 1

edited Dec 04 '12 at 08:57

answered Dec 04 '12 at 08:36

babaoqi

96
3

MySQL ORDER BY multiple column ASC and DESC

4 Answers4