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

Question

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

Answer 1

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

Answer 2

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