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

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

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

ex.

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.

CodePudding user response：

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.

CodePudding user response：

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;