Create calculated field from MIN() and MAX() values of another column, grouped by unique ID

I have a table that looks like this, containing information about an object’s position and the length of time it existed for (age):

Date        ID  Age x   y
2021-03-25  20  1   531 295
2021-03-25  20  2   478 272
2021-03-25  20  3   421 272
2021-03-26  20  1   478 286
2021-03-26  21  1   903 342

And I am trying to select the x position of a certain ID, when the age is at its minimum value for that ID (column named xStart), and when it is at its maximum value (column named xFin). IDs represent a different object on each day, so ID 20 on the 25th will not be the same object as ID 20 on the 26th.

I would like the resulting table to look something like this:

 Date           ID  Age x   y   xStart xEnd
 2021-03-25     20  1   531 295 531    421
 2021-03-25     20  2   478 272 531    421
 2021-03-25     20  3   421 272 531    421
 2021-03-26     20  1   478 286 478    some number
 2021-03-26     21  1   903 342 908    some other number

And that table could be grouped for each ID:

   Date         ID  MAX(Age) xStart xEnd
   2021-03-25   20  3        531    421
   2021-03-26   20  1        478    some number
   2021-03-26   21  1        908    some other number

Answer

You can use window functions, if I understand:

select distinct date, id,
       max(age) over (partition by date, id),
       first_value(x) over (partition by date, id order by age) as xstart,
       first_value(x) over (partition by date, id order by age desc) as xend
from t;

Advertisement

Answer