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!
34 Answers
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 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 @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.
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
