SQL Max value in a group

Question

I'm struggling to do something in SQL which I'm sure must be simple, but I can't figure it out. I want the MAX() value of a group, but I also want the value of another column in the same row as the max value. Here is an example table definition:

mysql> desc Sales;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| StoreID | int(11) | YES  |     | NULL    |       |
| Day     | int(11) | YES  |     | NULL    |       |
| Amount  | int(11) | YES  |     | NULL    |       |
+---------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

And here is some data for it:

mysql> SELECT * FROM Sales;
+---------+------+--------+
| StoreID | Day  | Amount |
+---------+------+--------+
|       1 |    1 |     44 |
|       1 |    2 |     31 |
|       1 |    3 |     91 |
|       2 |    1 |     93 |
|       2 |    2 |     32 |
|       2 |    3 |     41 |
|       3 |    1 |     48 |
|       3 |    2 |     95 |
|       3 |    3 |     12 |
+---------+------+--------+
9 rows in set (0.00 sec)

What I want to know is, what Day had the most sales (Amount) for each StoreID.

Now I know I can do this:

SELECT StoreID, MAX(Amount) FROM Sales GROUP BY StoreID;
+---------+-------------+
| StoreID | MAX(Amount) |
+---------+-------------+
|       1 |          91 |
|       2 |          93 |
|       3 |          95 |
+---------+-------------+
3 rows in set (0.00 sec)

That tells me the max amount of each store, but really what I'm after is the day that it occured. But I can't add Day back in to the query because it's not in the group by, and I don't think I really want to group by that value do I?

I'm not sure where to go from here.

In short, the results I want should look like this:

+---------+------+--------+
|       1 |    3 |     91 |
|       2 |    1 |     93 |
|       3 |    2 |     95 |
+---------+------+--------+

There are lots of other answers on Stack Overflow for the [tag:greatest-n-per-group] tag. — Bill Karwin, Dec 07 '19 at 00:41

score 2 · Answer 1 · answered Dec 06 '19 at 23:29

You want to filter. Here is one simple method using a correlated subquery:

select s.*
from s
where s.sales = (select max(s2.sales)
                 from sales s2
                 where s2.storeId = s.storeId
                );

If your data is on the large side, you will want an index on sales(storeId, sales).

LukStorms · Answer 2 · 2019-12-06T23:54:44.917

2

For the maximum amounts per store there won't exist a higher amount for the same store.

SELECT *
FROM Sales s
WHERE NOT EXISTS (
    SELECT 1 
    FROM Sales s2
    WHERE s2.StoreID = s.StoreID
      AND s2.Amount > s.Amount
)
ORDER BY Amount ASC, StoreID ASC;

Test here

edited Dec 06 '19 at 23:54

answered Dec 06 '19 at 23:39

LukStorms

28,916
5
31
45

score 1 · Answer 3 · answered Dec 07 '19 at 00:08

Typically you can just join the aggregating query back to get the rest of the row data...

SELECT s.*
FROM Sales AS s
INNER JOIN (
   SELECT StoreID, MAX(Amount) AS MaxAmount
   FROM Sales 
   GROUP BY StoreID
) AS m ON s.StoreID = m.StoredID AND s.Amount = m.MaxAmount
;

If there are multiple Sales with the MaxAmount for the same StoreID, the query will return all of them, not just one of them.

SQL Max value in a group

3 Answers3