How to add column in table grouped by value and max value in date column using sql query?

Question

I have a table:

id   date           val
1    10.08.2022     10
1    12.08.2022     11
1    08.08.2022     15
1    16.08.2022     9
2    02.07.2022     2
2    01.07.2022     4
2    30.07.2022     7

I want to create two new columns last_v and max_v which are equal to last val for each id by date and maximum val per id. So desired output is:

id   date           val   last_v   max_v
1    10.08.2022     10      9        15
1    12.08.2022     11      9        15
1    08.08.2022     15      9        15
1    16.08.2022     9       9        15
2    02.07.2022     2       2        7
2    01.07.2022     4       2        7
2    30.06.2022     7       2        7

How could I do that?

Join the table with a subquery that gets the last and max value for each group. — Barmar, Jan 09 '23 at 22:08
See https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql?rq=1 for how to get the last value in each group. — Barmar, Jan 09 '23 at 22:08
If you're using MySQL 8.x you can also do it in a single query using window functions. — Barmar, Jan 09 '23 at 22:09
@Barmar i just started to work with window functions. i don't see how to use it here(( — gh1222, Jan 09 '23 at 22:10
@PM77-1 I think it's the date, which is in DD.MM.YYYY format, but `30.07.2022` is a typo for `30.07.2022`. — Barmar, Jan 09 '23 at 22:13
Ugh, I also typoed it. I meant it's a typo for `30.06.2022`, which is in the desired output. — Barmar, Jan 09 '23 at 22:21

score 0 · Accepted Answer · answered Jan 09 '23 at 22:13

You can use window functions!

select t.*,
    first_value(val) over(partition by id order by dt desc) last_val,
    max(val) over(partition by id) max_val
from mytable t

Demo on DB Fiddle:

id	dt	val	last_val	max_val
1	2022-08-08	15	9	15
1	2022-08-10	10	9	15
1	2022-08-12	11	9	15
1	2022-08-16	9	9	15
2	2022-06-30	7	2	7
2	2022-07-01	4	2	7
2	2022-07-02	2	2	7

How to add column in table grouped by value and max value in date column using sql query?

1 Answers1