Select value by max date in multiple columns for Postgresql table

user_8645424 0
Viewed 33

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?

postgresql sql
1 Answers

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
user_23020320
Related