How to Select latest record for a customer from a table with column value from the first row-CodePudding

I have scenario like below

The sign up promo code, if applicable, is what the member used to create their membership. Our system currently creates a new line for a member each time they make a change, for example upgrading/downgrading their plan. The promo code currently doesn't get carried over and we want to make sure we're capturing this to properly analyze member acquisition.

EX: Membership_id : 1 Membership_id: 1 first signing in with promo code : New10 second upgrading to higher version without promocode third upgrading to another higher version without promocode

so we have 3 lines in database in first line we have promocode : New10 but other 2 lines don't have promocode .

As per below example latest record for membership id 1 is created on 2022-09-25, I want to select that latest record with promocode New10. Can you please suggest the MySQL logic for this ?

For example

Id        Membership_id     promocode createdtime             
1               1            New10    2022-08-01
2               1                     2022-09-14
3               1                     2022-09-25

Here , I want to select latest record for each membership id but want to select code column from first record when its created like below

expected output:

ID  Membership_id   promocode   createdtime
3       1   new10   25-09-2022

CodePudding user response：

Try it. It should work.

SELECT max(Id) as ID, 
Membership_id, 
(SELECT promocode from [table_name] group by Membership_id having min(Id)) as promocode, 
createdtime 
FROM [table_name] 
group by Membership_id;

CodePudding user response：

You can try with below subquery

SELECT t2.maxId AS ID,t1.Membership_id,t1.promocode,t2.createdtime  
FROM yourtable t1
JOIN
(
    SELECT max(id) as maxId,Membership_id,createdtime  
    FROM yourtable GROUP BY Membership_id
) t2 ON t1.Membership_id=t2.Membership_id
WHERE t1.t1.promocode IS NOT NULL

CodePudding user response：

Here's the simple solution.

select   max(id)          as id
        ,Membership_id   
        ,max(promocode)   as promocode
        ,max(createdtime) as createdtime
from     t
group by Membership_id

id	Membership_id	promocode	createdtime
3	1	New10	2022-09-25

Here's a solution that will give you the latest promocode in case a customer got more than one.

select   id
        ,Membership_id       
        ,recent_promocode as promocode
        ,createdtime
from     (
         select  *
                ,max(promocode) over(partition by Membership_id, grp order by createdtime) as recent_promocode
         from    (
                 select  *
                         ,count(case when promocode is not null then 1 end) over(partition by Membership_id order by createdtime) as grp
                         ,rank() over(partition by Membership_id order by createdtime desc) as rnk
                  from    t
                 ) t
         ) t
where    rnk = 1

Fiddle

CodePudding user response：

So, this is very simple, you can just group by membership_id and then, select the max of all columns in the group,

It will give you the latest id(because that's maximum) also will give you the promocode (because that's maximum as all other fields are NULL having ASCII value 0)

also will give you the latest date.

AND, then just select the membership_id as well as you are grouping based on it, so it's easy

select max(id),membership_id,max(promocode),max(createdtime) from tbl group by membership_id;

Here is a fiddle for it:- https://dbfiddle.uk/qYLwn9Oz

Click it and see for yourself.. Nice question though