I have a table that looks like this, containing information about an object’s position and the length of time it existed for (age):
x
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
Advertisement
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;