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

Mysql – How to identify the value which was assigned before the present value that is assigned?

There are multiple historical rows present for a customer in customer table and a id has been assigned which might have changed overtime

Name ID Date
Abhishek 1 23-08-2023
Abhishek 1 03-08-2023
Abhishek 2 17-06-2023
Abhishek 3 09-10-2022
Seema A 21-08-2023
Seema B 07-06-2022
Seema C 22-05-2020

Latest ids in the data

Name ID Date **
Abhishek 1 23-08-2023
Seema A 21-08-2023

The required output (ID values assigned just before these latest ones) is

Name ID Date **
Abhishek 2 17-06-2023
Seema B 07-06-2022

I tried lag function but in the response where there are multiple changes in the id the required output is not the second latest id but different ids involved

eg:

select * from (
select Name,`id,lag(id,1) over (partition by Name order by date) as
lag_id from customer_history)
Name ID lag_id
Abhishek 1 2
Abhishek 2 3
Seema A B
Tags:

2

Answers


  1. 0

    If this is MySQL then the dates should be formatted YYYY-mm-dd.

    The following query uses GROUP BY to remove the duplicate IDs and ROW_NUMBER():

    WITH t1 AS (
    SELECT Name, ID, MAX(Date), ROW_NUMBER() OVER (PARTITION BY Name ORDER BY MAX(Date) DESC) AS rn
    FROM customer_history
    GROUP BY Name, ID
)
SELECT * FROM t1 WHERE rn = 2;

    Output:

    Name ID MAX(Date) rn
    Abhishek 2 2023-06-17 2
    Seema B 2022-06-07 2

    Here’s a db<>fiddle

    Login or Signup to reply.
  2. 0

    If your date really is formatted like that, then you need to add a STR_TO_DATE() function to reformat the date before you can run the previous suggested answer. Therefore:

    WITH t1 AS (
  SELECT Name, ID, MAX(STR_TO_DATE(Date, '%d-%m-%Y')), 
        ROW_NUMBER() 
         OVER (PARTITION BY Name ORDER BY MAX(STR_TO_DATE(Date, '%d-%m-%Y')) DESC) AS rn
  FROM customer_history
  GROUP BY Name, ID
)
SELECT * FROM t1 WHERE rn = 2

    https://dbfiddle.uk/VdWN5TNq

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

Back To Top
Search