MySQL ORDER BY multiple column ASC and DESC

Question

I have 2 MYSQL tables  users and scores  Detail    users table       scores table      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     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   Thanks in advance

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] MySQL ORDER BY multiple column ASC and DESC

Examples related to mysql

Examples related to sql-order-by

Examples related to multiple-columns