Most Recent Record -- several columns

Question

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!

Answer 1

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