Max of a Date field into another field in Postgresql

Question

I have a postgresql table wherein I have few fields such as id and date. I need to find the max date for that id and show the same into a new field for all the ids. SQLFiddle site was not responding so I have an example in the excel. Here is the screenshot of the data and the output for the table.

Mureinik · Accepted Answer · 2016-06-05 04:58:40Z

1

You could use the windowing variant of max:

SELECT id, date, MAX(date) OVER (PARTITION BY id)
FROM   mytable

answered Jun 5, 2016 at 4:58

Mureinik

316k54 gold badges400 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

neouser99 · Accepted Answer · 2016-06-05 04:58:03Z

0

Something like this might work:

WITH maxdts AS (
  SELECT id, max(dt) maxdt FROM table GROUP BY id
)
SELECT id, date, maxdt FROM table t, maxdts m WHERE t.id = m.id;

Keep in mind without more information that this could be a horribly inefficient query, but it will get you what you need.

answered Jun 5, 2016 at 4:58

neouser99

1,8271 gold badge10 silver badges24 bronze badges

Collectives™ on Stack Overflow

Max of a Date field into another field in Postgresql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related