Pulling all ID with the latest date

Question

Say I have multiple columns and three different rows. I want to pull the all the ID with the latest date 20220205 (for Ann) and 20220208 (for Lima) to get the correct package code. How do I code it out in the where statement?

ID	Name	pkg_date	package
11	Ann	20220205	R
11	Ann	20220101	A
11	Ann	20211101	U
22	Lima	20210708	B
22	Lima	20220208	A

What should we do if dates are *equal*? E.g. `33 John 20230209 R` and `33 John 20230209 U`? — Dmitry Bychenko, Feb 09 '23 at 23:09
How this is done could depend on what RDBMS is used. Add appropriate tag. Likely this will involve a TOP N nested query. — June7, Feb 10 '23 at 00:11
Review https://stackoverflow.com/questions/74524646/msaccess-query-to-return-result-set-of-earliest-rows-with-a-unique-combination/74525068#74525068 — June7, Feb 10 '23 at 00:20
@DmitryBychenko Both of them should be pulled as well. However, finger crossed there should not be any, because the package_date column is kind of like an update, and they only update once in a few months. — dspassion, Feb 10 '23 at 03:10
@June7 I have more than 10 columns so I am not sure if the grouping method would work, it might get very messy. Let me try. — dspassion, Feb 10 '23 at 03:11

score 0 · Answer 1 · answered Feb 10 '23 at 04:28

You can use one of the rank-related window functions (rank, dense_rank, or row_number). Here is an example which uses dense_rank function.

SELECT *
FROM (
    select id, 
           name, 
           pkg_date, 
           package,
           dense_rank() OVER(PARTITION BY id ORDER BY pkg_date DESC) rk
    from table_name) t
WHERE rk = 1

score 0 · Answer 2 · answered Feb 10 '23 at 04:41

0

You can use an inner join with the max date:

SELECT ID, NAME, PKG_DATE, PACKAGE 
FROM table t INNER JOIN 
(SELECT max(PKG_DATE), ID, NAME FROM table GROUP BY ID, NANE) i 
on i.ID = t.ID;

answered Feb 10 '23 at 04:41

stylepatrick

173
2
11

Tom Meacham · Answer 3 · 2023-02-11T22:56:04.607

Try the new MAX_BY function:

select
      id
    , name
    , max_by(package, pkg_date) as latest_pkg_code
from test_table
    group by 1,2;

Result:

ID	NAME	LATEST_PKG_CODE
11	Ann	R
22	Lima	A

Here is a sample script:

-- create table
create or replace transient table test_table (
      id int
    , name varchar(50)
    , pkg_date date
    , package varchar(1)
    );

-- insert data
insert into test_table
  values
   (11,'Ann','2022-02-05','R')
  ,(11,'Ann','2022-01-01','A')
  ,(11,'Ann','2021-11-01','U')
  ,(22,'Lima','2021-07-08','B')
  ,(22,'Lima','2022-02-08','A');

-- test table
select * from test_table;

-- get package code for max date for each name/id
select
      id
    , name
    , max_by(package, pkg_date) as latest_pkg_code
from test_table
    group by 1,2;

Pulling all ID with the latest date

3 Answers3