I have 2 MYSQL tables, users and scores. Detail:
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!
This question is related to
mysql
sql-order-by
multiple-columns
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
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.
Source: Stackoverflow.com