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!

Answer 1

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 

Answer 2

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