I want to know if there is an easier way to grab the most recent record per unique ID than what I'm doing.
right now I use a CTE -- add a RN partitioned by the date, with a follow up select statement that filters for RN = 1.
I have a fellow analyst who uses group by unique ID having date = max(date), but that never seems to work for me and I can't figure out why ---the error tells me to group all the other columns. Once I group all the columns, it produces the same result (all records for each unique ID).
Hoping someone comes in clutch with a solution!
I like the CTE method, but here's one alternative:
Create table and insert data:
create table my_table (
user_id integer,
some_value varchar(20),
trans_date date
);
insert into my_table values
(1, 'a', '2020-05-01'), --this is the max date
(1, 'b', '2020-04-12'),
(1, 'c', '2017-10-18'),
(1, 'd', '2016-11-21'),
(1, 'e', '2013-06-08'),
(2, 'gg', '2020-04-12'), -- this is the max date
(2, 'hh', '2016-11-21'),
(2, 'jj', '2015-05-21'),
(2, 'bb', '2013-06-08');
Query for obtaining all columns per user based on max date:
select *
from my_table t
where trans_date in (
select max(trans_date) from my_table m where user_id = t.user_id)
Output:
user_id some_value trans_date
1 a 2020-05-01
2 gg 2020-04-12