Select value by max date in multiple columns for Postgresql table

Question

Let's say that I have a query that returns the following information:

+-----------+----------+----------+----------+----------+
| ForeignId | ScoreOne | DateOne  | ScoreTwo | DateTwo  |
+-----------+----------+----------+----------+----------+
| 1         |    1     |2021-06-20|    10    |2021-11-15|
| 1         |    2     |2021-06-21|    11    |2021-11-14|
| 1         |    3     |2021-06-22|    13    |2021-11-13|
| 1         |    4     |2021-06-23|     9    |2021-11-12|
| 1         |    3     |2021-06-24|     7    |2021-11-11|
| 1         |    1     |2021-06-25|     5    |2021-11-10|
| 1         |    2     |2021-06-26|     8    |2021-11-09|
| 2         |    5     |2021-02-11|     9    |2020-07-01|
| 2         |    8     |2021-02-12|     9    |2020-07-02|
| 2         |    9     |2021-02-13|     3    |2020-07-03|
| 2         |    7     |2021-02-14|     5    |2020-07-04|
+-----------+----------+----------+----------+----------+

In this case, I want to bring the ScoreOne value for the row with the max(DateOne), and the same for the ScoreTwo that it's in the same row as the max(DateTwo). As you can see, the max(DateOne) is not in the same row as the max(DateTwo). So basically the result will be this one:

+-----------+----------+----------+----------+----------+
| ForeignId | ScoreOne | DateOne  | ScoreTwo | DateTwo  |
+-----------+----------+----------+----------+----------+
| 1         |    2     |2021-06-26|    10    |2021-11-15|
| 2         |    7     |2021-02-14|    5     |2020-07-04|
+-----------+----------+----------+----------+----------+

I understand that my query will use a GROUP BY ForeignId and it also uses both max(DateOne) and max(DateTwo), but how can I achieve this result?

Stefanov.sm · Accepted Answer · 2022-07-27 13:11:25Z

Use window functions. the_table CTE is a mimic of your actual data.

with the_table (foreignid,scoreone,dateone,scoretwo,datetwo) as
(
 values
  (1, 1, '2021-06-20'::date, 10, '2021-11-15'::date),    
  (1, 2, '2021-06-21', 11, '2021-11-14'),    
  (1, 3, '2021-06-22', 13, '2021-11-13'),    
  (1, 4, '2021-06-23',  9, '2021-11-12'),    
  (1, 3, '2021-06-24',  7, '2021-11-11'),    
  (1, 1, '2021-06-25',  5, '2021-11-10'),    
  (1, 2, '2021-06-26',  8, '2021-11-09'),    
  (2, 5, '2021-02-11',  9, '2020-07-01'),    
  (2, 8, '2021-02-12',  9, '2020-07-02'),    
  (2, 9, '2021-02-13',  3, '2020-07-03'),    
  (2, 7, '2021-02-14',  5, '2020-07-04')     
) 
select distinct on (foreignid)
    foreignid,
    first_value(scoreone) over win_one as "ScoreOne",
    first_value(dateone)  over win_one as "DateOne",
    first_value(scoretwo) over win_two as "ScoreTwo",
    first_value(datetwo)  over win_two as "DateTwo"
from the_table
window win_one as (partition by foreignid order by dateone desc),
       win_two as (partition by foreignid order by datetwo desc);

foreignid	ScoreOne	DateOne	ScoreTwo	DateTwo
1	2	2021-06-26	10	2021-11-15
2	7	2021-02-14	5	2020-07-04

Collectives™ on Stack Overflow

Select value by max date in multiple columns for Postgresql table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related