CODEWITHSUNDEEP
mysql
Daniel Crocker
Daniel Crocker

Reputation: 45

MySQL grouping (newest first for each "group")

I've been struggling with this one for a while now, and it seems so simple yet I can't wrap my head around it.

My initial query is as follows

SELECT `updated`, `price`, `condition` 
FROM game_prices 
WHERE `game_id` = '1960' 
AND `source` = '0' 
AND `updated` > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 YEAR)) 
ORDER BY `updated` DESC;

This gives the following results...

updated     price   condition
1496691650  9.89    1
1496009100  0.01    0
1494799503  5.99    1
1489881902  9.89    1
1487808171  7.66    1

Perfect. However, what I'd like to do is select only the most recent of each unique "condition". Condition can be 0, 1, or 2.

To confirm updated is a unix timestamp.

So I perform a sub-query on the results of the initial query...

SELECT `updated`, `price`, `condition` 
FROM ( 
        SELECT `updated`, `price`, `condition` 
        FROM game_prices 
        WHERE `game_id` = '1960' 
        AND `source` = '0' 
        AND `updated` > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 YEAR)) 
        ORDER BY `updated` DESC 
    ) AS sub 
GROUP BY `condition` 
ORDER BY `price` ASC;

In my head this should work, but I am getting the following results...

updated     price   condition
1496009100  0.01    0
1487808171  7.66    1

As you can see, it is picking the oldest record for each "condition". What am I doing wrong here?

Upvotes: 0

Views: 45

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

One method uses a correlated subquery:

SELECT gp.*
FROM game_prices gp
WHERE game_id = '1960' AND
      source = '0' AND
      updated > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 YEAR) AND
      updated = (SELECT MAX(gp2.updated)
                 FROM game_prices gp2
                 WHERE gp2.game_id = gp.game_id AND
                       gp2.source = gp.source AND
                       gp2.condition = gp.condition AND
                       gp2.updated > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 YEAR))
                )
ORDER BY `updated` DESC;

Upvotes: 1

Mihai
Mihai

Reputation: 26784

Here is another way without subqueries

SELECT MAX(`updated`), SUBSTRING_INDEX(GROUP_CONCAT(`price` ORDER BY updated DESC),',',1), `condition` 
FROM game_prices 
WHERE `game_id` = '1960' 
AND `source` = '0' 
AND `updated` > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 YEAR)) 
GROUP BY `condition`
ORDER BY SUBSTRING_INDEX(GROUP_CONCAT(`price` ORDER BY updated DESC),',',1) ;

As to your last query,you don`t have aggregate conditions on your 2 columns not in the GROUP BY,so you get indeterminate values(random)

Upvotes: 1

Related Questions