Question 113314 in Mysql Question posted in
The official MySQL documentation can be found here.

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

I have scenario like below

Sign up promo code

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
Tags:

3

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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 first promocode in case a customer got more than one.

    select   id
        ,Membership_id       
        ,first_promocode as promocode
        ,createdtime
from     (
         select  *
                ,max(case when grp = 1 then promocode end) over(partition by Membership_id order by createdtime) as first_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

    Login or Signup to reply.
  3. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search