I have a table that keeps activity records, containing the date of registration and other information about the activity performed. I would like to make a query that would return one more column in the table, containing the maximum record date.
I don’t think it’s too complicated, but my knowledge is limited on the subject. Would a join between tables be the solution? How can I do it?
my original table:
| ID | Value | Date |
|---|---|---|
| 01 | 34 | 2022-02-15 |
| 01 | 42 | 2022-02-08 |
| 02 | 12 | 2022-02-08 |
| 02 | 30 | 2022-02-01 |
I need to get:
| ID | Value | Date | Date_max |
|---|---|---|---|
| 01 | 34 | 2022-02-15 | 2022-02-15 |
| 01 | 42 | 2022-02-08 | 2022-02-15 |
| 02 | 12 | 2022-02-08 | 2022-02-15 |
| 02 | 30 | 2022-02-01 | 2022-02-15 |
I just need a column with the global maximum value. It will be the same value for all rows.
>Solution :
You can use a window function:
select id, value, date, max(date) over () as date_max
from the_table
order by id, date desc;