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

smritimishra
August 28, 2023
205 views
0 votes
2 Answers

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

Answers

- user1191247
- August 25, 2023 at 4:39 pm
- 0 votes
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.

- FanoFN
- August 28, 2023 at 6:41 am
- 0 votes
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.