MYSQL: Get one column value based on aggregate/group by of other columns

Question

Let's say I have a table with 3 columns

USER     | MEAL          | TIMESTAMP
"user1"  | "bagel"       | 2022-01-01 8:00:00
"user1"  | "sandwich"    | 2022-01-01 12:00:00
"user1"  | "spaghetti"   | 2022-01-01 18:00:00
"user2"  | "cereal"      | 2022-01-01 9:30:00
"user2"  | "soup"        | 2022-01-01 12:30:00
"user2"  | "pizza"       | 2022-01-01 20:00:00

How can I SELECT the most recent MEAL for each USER? My assumption is something like:

SELECT MEAL, USER
FROM MEAL_TABLE
GROUP BY USER
HAVING MAX(TIMESTAMP)

but this doesn't work because MEAL is not aggregated.

This question is answered elsewhere but I don't believe the question itself is a duplicate so I'll leave it here in case it captures anyone else's questions that aren't specific to an aggregation function. — Lucian Thorr, Jul 29 '22 at 18:37

The Impaler · Answer 1 · 2022-07-28T18:32:47.493

1

You can use ROW_NUMBER() to identify the rows you want. Then filtering out is trivial.

For exmaple:

select *
from (
  select *,
    row_number() over(partition by user order by timestamp desc) as rn
  from t
) x
where rn = 1 -- we only want the last one from each group

edited Jul 28 '22 at 18:32

answered Jul 28 '22 at 18:24

The Impaler

45,731
9
39
76

score 0 · Answer 2 · answered Jul 28 '22 at 18:25

You could use a common table expression to get the max date per user and then join to it on user and date.

Or here's an alternative method:

select meal, user
from meal_table t
where trans_date in (
  select max(trans_date) from meal_table m where user = t.user)

MYSQL: Get one column value based on aggregate/group by of other columns

2 Answers2