MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE customer_history (
  Name VARCHAR(20) NOT NULL,
  ID CHAR(1) NOT NULL,
  Date VARCHAR(50) NOT NULL
);
INSERT INTO customer_history VALUES
  ('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');

Records: 7  Duplicates: 0  Warnings: 0


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


Name
ID
MAX(Date)
rn


Abhishek
1
23-08-2023
1

Abhishek
2
17-06-2023
2

Abhishek
3
09-10-2022
3

Seema
C
22-05-2020
1

Seema
A
21-08-2023
2

Seema
B
07-06-2022
3


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


Name
ID
MAX(Date)
rn


Abhishek
2
17-06-2023
2

Seema
A
21-08-2023
2


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


Name
ID
MAX(STR_TO_DATE(Date, '%d-%m-%Y'))
rn


Abhishek
2
2023-06-17
2

Seema
B
2022-06-07
2

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