group by po jednom polju pomoc

[ duka94 @ 26.06.2019. 22:39 ] @

Imam dve tabele users(id, username), games(points, created_at, user_id) i potrebno mi je da izvucem usere sa max poenima sto mi kod ispod uspesno radi:

Code:

SELECT u.username, g.points
FROM users u, (
       SELECT games.user_id, max(games.points) as points
       FROM games
       GROUP BY user_id) g
WHERE u.id = g.user_id
order by points DESC

problem se desava kad treba da dodam games.created_at, onda moram da ga uvrstim u group by tako da mi onda vrati sve redove iz tabele games umesto samo usere sa maksimalnim poenima. Kako da selektujem i created_at iz games tabele a da "ne poremetim" trenutni select? Hvala unapred

[ Deunan @ 26.06.2019. 23:31 ] @

Moze ovako:

Code:

SELECT users.id, users.username, games.points, games.created_at FROM users INNER JOIN games ON users.id = games.user_id
WHERE
(users.id, games.points) IN (SELECT user_id, MAX(points) FROM games GROUP BY user_id)