A
A
apptimeru2016-02-28 14:44:17
SQL
apptimeru, 2016-02-28 14:44:17

How to compose a query so that the largest value is selected when grouping?

Hi all. For a day now I have been trying to figure out a seemingly simple request, but the couple does not work.
The request itself:

SELECT meta.meta_value, game.title FROM meta
INNER JOIN game ON game.id = meta.id_game
WHERE  game.month = 2 AND game.year = 2016 AND meta.meta_key = 'video' 
GROUP BY meta.id_games 
ORDER BY game.score + game.like DESC LIMIT 10

Approximate view of tables
game                meta

id                  id
title               id_games
month               meta_key
year                meta_value
score
like

If done without grouping, we get the following:
The bottom line is, any game can have several videos in the meta table with meta_key (video) you need to pull out videos of popular games for this, sorting by the score and like fields of the game table , but only one video from every game.
This query works well, but there is a problem, when grouping, it takes the first videos with the smallest meta.id, which is logical, but it is necessary that the result includes the last added video i.e. the maximum meta.id
If you make a request without grouping, you can see that all videos of one game are selected, but you need to select only the last video of each game
LW3t1Z8zEi0MmPVaFjLIQ.png
I hope I managed to explain)

Answer the question

In order to leave comments, you need to log in

3 answer(s)
A
Alexey Ukolov, 2016-02-28
@apptimeru

SELECT MAX(meta.meta_value), game.title
FROM meta
INNER JOIN base ON game.id = meta.id_game
WHERE  game.month = 2 AND game.year = 2016 AND meta.meta_key = 'video' 
GROUP BY meta.id_games 
ORDER BY game.score DESC, game.like DESC
LIMIT 10

W
werw, 2016-02-28
@werw

MAX(meta.id)

R
res2001, 2016-02-28
@res2001

Something like this:

select meta.meta_value, game.title
from game
join (SELECT game.id as id_games, max(meta.id) as id_meta
FROM meta
JOIN game ON game.id = meta.id_game and game.month = 2 AND game.year = 2016
WHERE meta.meta_key = 'video' 
GROUP BY game.id 
ORDER BY game.score + game.like DESC LIMIT 10) A on A.id_games=game.id
join meta on meta.id=A.id_meta

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question