SQL/Postgres: Get Single ACTIVE=Y Record from Group, ELSE Latest Date Record from Group

user_1005607 0
Viewed 42

Using Postgres:

My AGREEMENT_T lists records that belong to a particular agreement_group_id. At any time, within a Group, either 1 is ACTIVE=Y, or 0 (at most 1).

ID   AGREEMENT_GROUP_ID  ACTIVE  FIELD1  FIELD2 STATUS_ACTION_DATE
------------------------------------------------------------------
1    55                  N       45      50     01-Oct-2021 00:03:05
2    55                  Y       78      70     30-Sep-2021 03:04:05
3    100                 N       44      4      02-Oct-2021 04:00:01
4    101                 N       1       2      02-Oct-2021 04:01:05
5    101                 N       0       1      05-Oct-2021 11:13:54
6    102                 Y       3       3      05-Oct-2021 11:47:45
7    102                 N       4       4      05-Oct-2021 11:48:49

I need to get a result that lists Agreement Group ID paired with

  1. If ACTIVE=Y is available within a Group, then that Agreement ID
  2. Else, the ID with the latest STATUS_ACTION_DATE for that Group

ex.

55   2
100  3
101  5
102  6

So far, I can pull up the latest STATUS_ACTION_DATE using Postgres' array_agg(..)[1] as follows,

SELECT agreement_group_id, (array_agg(id ORDER BY status_action_date DESC))[1] AS val
FROM ets.agreement_t 
GROUP BY agreement_group_id 
ORDER BY agreement_group_id

But is there a quick way to integrate that with finding ACTIVE=Y, maybe without using an extra SELECT EXISTS? Also something that performs well.

postgresql sql
2 Answers

You may use ROW_NUMBER to create a filter as shown below:

Query #1

SELECT
     *
FROM (
   SELECT 
        *, 
        ROW_NUMBER() OVER (
             PARTITION BY agreement_group_id 
             ORDER BY ACTIVE DESC, STATUS_ACTION_DATE DESC
        ) as rn
   FROM agreement_t
) t
WHERE rn=1;
id agreement_group_id active field1 field2 status_action_date rn
2 55 Y 78 70 2021-09-30T00:00:00.000Z 1
3 100 N 44 4 2021-10-02T00:00:00.000Z 1
5 101 N 0 1 2021-10-05T00:00:00.000Z 1
6 102 Y 3 3 2021-10-05T00:00:00.000Z 1

Query #2

SELECT
     agreement_group_id , id
FROM (
   SELECT 
        *, 
        ROW_NUMBER() OVER (
             PARTITION BY agreement_group_id 
             ORDER BY ACTIVE DESC, STATUS_ACTION_DATE DESC
        ) as rn
   FROM agreement_t
) t
WHERE rn=1;
agreement_group_id id
55 2
100 3
101 5
102 6

View on DB Fiddle

Let me know if this works for you.

user_43071360

You can use distinct on and get a little creative with order by: (see demo)

select distinct on (agreement_group_id)                                                                                             
       agreement_group_id, id                                                                                                                           
  from <your table>
 order by agreement_group_id                                                                                                        
        , case when active = 'Y' then 0 else 1 end                                                                                  
        , status_action_date desc;
user_76238560
Related