I have a table where I am trying to find all of the ID's that have an associated confirmed=1 for its maximum date. The primary key is the ID & Date.

In this scenario, only Jim should be returned because the maximum date is 20210201 & is marked as 1. Bob is not returned because 20210301 is the maximum date is marked as 0.

CodePudding user response：

You can use the MAX analytic function to find the latest date for each ID then filter for records that match the max date and are confirmed.

WITH
    sample_data (id, dt, confirmed)
    AS
        (SELECT 'Bob', '20210101', 1 FROM DUAL
         UNION ALL
         SELECT 'Bob', '20210201', 1 FROM DUAL
         UNION ALL
         SELECT 'Bob', '20210301', 0 FROM DUAL
         UNION ALL
         SELECT 'Jim', '20210101', 0 FROM DUAL
         UNION ALL
         SELECT 'Jim', '20210201', 1 FROM DUAL)
SELECT DISTINCT id
  FROM (SELECT d.id,
               d.dt,
               confirmed,
               MAX (d.dt) OVER (PARTITION BY d.id ORDER BY d.dt DESC)     AS max_dt
          FROM sample_data d)
 WHERE dt = max_dt AND confirmed = 1;

CodePudding user response：

You can use an analytic function such as ROW_NUMBER()

SELECT DISTINCT id
  FROM
  ( SELECT t.*, 
           ROW_NUMBER() OVER ( PARTITION BY id
                                   ORDER BY TO_DATE("date",'yyyymmdd') DESC ) AS rn
      FROM t ) 
 WHERE rn = 1
   AND confirmed = 1

sorting descendingly by "date" and grouping by "date" will yield the desired records with maximum date values for each primary key value

PS. "date" is assumed to be a string, and converted to date type

Demo