How to make a join with the same table to insert the maximum column value?

user_18279262 0
Viewed 62

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.

postgresql sql
3 Answers

You can use a window function:

select id, value, date, max(date) over () as date_max
from the_table
order by id, date desc;
user_3303150

Another approach: getting the max date with subquery:

select id, value, date, (select max(date) from original_table) Date_max
    from original_table o
    order by id
user_86516010

Lots of ways to do this:

  • Analytic function if it's available to you.
  • Inline Select query
  • cross join

. The 1st two have been covered in answers already so here's a 3rd option. The analytic would be the most "modern" way to handle this I can think of.

SELECT A.*, B.Date_Max
FROM Table A
CROSS JOIN (SELECT max(Date) Date_Max FROM Table Sub) B
user_10164350
Related