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:

I need to get:

I just need a column with the global maximum value. It will be the same value for all rows.

CodePudding user response：

You can use a window function:

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

CodePudding user response：

max()over() window function will be better approach for it. If you want to have ID wise maximum date:

select id, value, date, max(date) over (partition by id) as Date_max
from original_table
order by id

Or if you need to do it with subquery then:

select id, value, date, (select max(date) from original_table where o.id=ot.id) Date_max
    from original_table o
    order by id

Getting the max date irrespective of id with subquery:

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

CodePudding user response：

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