postgresql - using aggregation and max

Question

I have this table:

abs=# \d import.vw_rpt_certification 
                 Table "import.vw_rpt_certification"
            Column            | Type | Collation | Nullable | Default 
------------------------------+------+-----------+----------+---------
 user_id                      | text |           |          | 
 cert_object_id               | text |           |          | 
 version                      | text |           |          | 
 cert_is_active               | text |           |          | 
 cert_modify_dt               | text |           |          | 
 period_type                  | text |           |          | 
 user_cert_status             | text |           |          | 
 first_period_completion_date | text |           |          | 
 period_expiration_date       | text |           |          | 
 period_due_date              | text |           |          | 
 user_lo_active               | text |           |          | 
 user_lo_last_action_dt       | text |           |          | 
 cert_is_on_hold              | text |           |          | 
 user_lo_assigned_dt          | text |           |          | 
 user_lo_last_modified_dt     | text |           |          | 
 user_lo_status_id            | text |           |          | 
 user_lo_status               | text |           |          | 
 user_cert_is_archived        | text |           |          | 
 is_current_period            | text |           |          | 



select * from import.vw_rpt_certification limit 10;

 user_id |            cert_object_id            | version | cert_is_active |      cert_modify_dt      |   period_type    |             user_cert_status             | first_period_completion_date | period_expiration_date |   period_due_date    | user_lo_active | user_lo_last_action_dt  | cert_is_on_hold |   user_lo_assigned_dt    | user_lo_last_modified_dt | user_lo_status_id | user_lo_status | user_cert_is_archived | is_current_period 
---------+--------------------------------------+---------+----------------+--------------------------+------------------+------------------------------------------+------------------------------+------------------------+----------------------+----------------+-------------------------+-----------------+--------------------------+--------------------------+-------------------+----------------+-----------------------+-------------------
 109485  | 05491b56-bea6-436c-bb2d-04d67ec546a4 | 1       | True           | 2017-06-27T15:21:23.993Z | Initial relative | In Progress                              |                              |                        |                      | 1              |                         | False           |                          |                          | 131072            | Not Activated  | False                 | 1
 73084   | 4cb5d794-3191-4d18-bf08-aa3c67e9f53a | 1       | True           | 2017-12-20T13:29:30.223Z | renewal relative | Certified                                | 2015-11-04T23:00:00Z         | 2018-11-04T23:59:00Z   | 2018-10-04T23:59:00Z | 1              |                         | False           |                          |                          | 131072            | Not Activated  | False                 | 0
 128339  | a9bf53a1-3060-43ba-852a-85ea3414101e | 1       | True           | 2017-12-20T13:54:52.7Z   | Initial relative | In Progress                              |                              |                        |                      | 1              |                         | False           |                          |                          | 131072            | Not Activated  | False                 | 1
 191564  | 4cb5d794-3191-4d18-bf08-aa3c67e9f53a | 1       | True           | 2017-12-20T13:29:30.223Z | Initial relative | In Progress                              |                              |                        |                      | 1              |                         | False           |                          |                          | 131072            | Not Activated  | False                 | 1
 110211  | 0ccd9922-85fa-45de-bd87-257b77e2a5a3 | 1       | True           | 2018-04-25T08:27:00Z     | Initial relative | In Progress                              |                              |                        |                      | 0              | 2010-12-31T23:00:00Z    | False           | 2010-12-31T23:00:00Z     | 2010-12-31T23:00:00Z     | 4                 | Approved       | False                 | 1
 203012  | a9bf53a1-3060-43ba-852a-85ea3414101e | 1       | True           | 2017-12-20T13:54:52.7Z   | Initial relative | In Progress                              |                              |                        |                      | 0              | 2018-02-07T14:31:23.25Z | False           | 2018-02-07T14:31:22.943Z | 2018-02-07T14:31:00Z     | 4                 | Approved       | False                 | 1
 81973   | 4cb5d794-3191-4d18-bf08-aa3c67e9f53a | 1       | True           | 2017-12-20T13:29:30.223Z | Initial relative | Expired                                  | 2015-02-25T23:00:00Z         | 2015-02-25T23:59:00Z   |                      | 0              | 2015-02-26T06:00:00Z    | False           | 2015-02-26T06:00:00Z     | 2015-02-26T06:00:00Z     | 64                | Completed      | False                 | 0
 72012   | b39e8cbe-3030-4119-ac04-af5d623f443b | 1       | True           | 2017-06-27T15:20:31.88Z  | Initial relative | In Progress                              |                              |                        |                      | 1              |                         | False           |                          |                          | 131072            | Not Activated  | False                 | 1
 93502   | dc28893a-5fdc-4fd2-b394-4cec5fc5a5bb | 1       | True           | 2017-06-27T15:25:28.167Z | Initial relative | Certified (Renewal In Progress)/Past Due | 2014-06-04T23:00:00Z         | 2014-06-04T23:59:00Z   |                      | 0              | 2014-06-05T05:00:00Z    | False           | 2014-06-04T05:00:00Z     | 2014-06-04T05:00:00Z     | 64                | Completed      | False                 | 0
 128588  | dc28893a-5fdc-4fd2-b394-4cec5fc5a5bb | 1       | True           | 2017-06-27T15:25:28.167Z | Initial relative | Certified                                | 2016-09-01T23:00:00Z         | 2016-09-01T23:59:00Z   |                      | 0              | 2016-09-02T05:00:00Z    | False           | 2016-09-01T05:00:00Z     | 2016-09-01T05:00:00Z     | 64                | Completed      | False                 | 1

for some user_id and cert_object_id there are multiple rows:

select * from import.vw_rpt_certification  where user_id = '81634' and cert_object_id = '4cb5d794-3191-4d18-bf08-aa3c67e9f53a';

 user_id |            cert_object_id            | version | cert_is_active |      cert_modify_dt      |   period_type    | user_cert_status | first_period_completion_date | period_expiration_date |   period_due_date    | user_lo_active |  user_lo_last_action_dt  | cert_is_on_hold |   user_lo_assigned_dt    | user_lo_last_modified_dt | user_lo_status_id | user_lo_status | user_cert_is_archived | is_current_period 
---------+--------------------------------------+---------+----------------+--------------------------+------------------+------------------+------------------------------+------------------------+----------------------+----------------+--------------------------+-----------------+--------------------------+--------------------------+-------------------+----------------+-----------------------+-------------------
 81634   | 4cb5d794-3191-4d18-bf08-aa3c67e9f53a | 1       | True           | 2017-12-20T13:29:30.223Z | renewal relative | Certified        | 2015-04-22T23:00:00Z         | 2018-01-26T23:59:00Z   |                      | 0              | 2018-02-21T12:45:49.153Z | False           | 2018-02-21T12:37:56.473Z | 2018-02-21T12:46:00Z     | 64                | Completed      | False                 | 1
 81634   | 4cb5d794-3191-4d18-bf08-aa3c67e9f53a | 1       | True           | 2017-12-20T13:29:30.223Z | Initial relative | Certified        | 2015-04-22T23:00:00Z         | 2015-04-22T23:59:00Z   |                      | 0              | 2015-04-23T05:00:00Z     | False           | 2015-04-23T05:00:00Z     | 2017-09-28T23:02:00Z     | 64                | Completed      | False                 | 0
 81634   | 4cb5d794-3191-4d18-bf08-aa3c67e9f53a | 1       | True           | 2017-12-20T13:29:30.223Z | renewal relative | Certified        | 2015-04-22T23:00:00Z         | 2021-04-22T23:59:00Z   | 2021-03-22T23:59:00Z | 1              |                          | False           |                          |                          | 131072            | Not Activated  | False                 | 0

for those cases, I have to pick the row with the higher value of the column period_expiration_date (that column can also be an empty string).

I tried something in this way:

with s1 as (
  select distinct
    user_id,
    cert_object_id,
    COALESCE(nullif(period_expiration_date,''), NULL)::timestamptz as ped
  from import.vw_rpt_certification
  group by user_id, cert_object_id, ped
), s2 as (
    select dd.*, max(ped)
    from s1 dd
    group by dd.user_id, dd.cert_object_id, dd.ped
)
  select * from s2;


 user_id |            cert_object_id            |          ped           |          max           
---------+--------------------------------------+------------------------+------------------------
 81634   | 4cb5d794-3191-4d18-bf08-aa3c67e9f53a | 2021-04-23 01:59:00+02 | 2021-04-23 01:59:00+02
 81634   | 4cb5d794-3191-4d18-bf08-aa3c67e9f53a | 2018-01-27 00:59:00+01 | 2018-01-27 00:59:00+01
 81634   | 4cb5d794-3191-4d18-bf08-aa3c67e9f53a | 2015-04-23 01:59:00+02 | 2015-04-23 01:59:00+02

but I'm a bit lost, any help about this?

I want to generate a table with the same columns of import.vw_rpt_certification but that kept only one rows for each couple (user_id, cert_object_id)

Mayank Porwal · Accepted Answer · 2018-10-18 08:56:26Z

4

Try this:

Select user_id, cert_object_id, period_expiration_date 
FROM
(
Select * , 
ROW_NUMBER() OVER(partition by user_id, cert_object_id 
                  ORDER by period_expiration_date desc) as rnk
from import.vw_rpt_certification
)a
WHERE rnk = 1;

answered Oct 18, 2018 at 8:56

Mayank Porwal

34.2k9 gold badges45 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Biegeleisen · Accepted Answer · 2018-10-18 08:56:03Z

1

Why not use ROW_NUMBER here:

WITH cte AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY user_id, cert_object_id
            ORDER BY period_expiration_date DESC) rn
    FROM import.vw_rpt_certification
)

SELECT *
FROM cte
WHERE rn = 1;

answered Oct 18, 2018 at 8:56

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2018-10-18 12:14:43Z

0

In Postgres, distinct on is usually the best approach:

select distinct on (user_id, cert_object_id) rc.*
from import.vw_rpt_certification rc
order by user_id, cert_object_id, 
         period_expiration_date desc;

This can take direct use of an index on (user_id, cert_object_id, period_expiration_date).

edited Oct 18, 2018 at 12:14

answered Oct 18, 2018 at 10:39

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

Luke Over a year ago

this does not address the question: I want to generate a table with the same columns of import.vw_rpt_certification but that kept only one rows for each couple (user_id, cert_object_id). Also: this is a prefiltering made on an intermediate table before insertion on the target table which will have a unique constraint on (user_id, cert_object_id).

Gordon Linoff Over a year ago

@Luke . . . The answer is now what I intended it to be.

Collectives™ on Stack Overflow

postgresql - using aggregation and max

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related