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 DATE NOT NULL
);
INSERT INTO customer_history VALUES
  ('Abhishek', 1, '2023-08-23'),
  ('Abhishek', 1, '2023-08-03'),
  ('Abhishek', 2, '2023-06-17'),
  ('Abhishek', 3, '2022-10-09'),
  ('Seema', 'A', '2023-08-21'),
  ('Seema', 'B', '2022-06-07'),
  ('Seema', 'C', '2020-05-22');

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
2023-08-23
1

Abhishek
2
2023-06-17
2

Abhishek
3
2022-10-09
3

Seema
A
2023-08-21
1

Seema
B
2022-06-07
2

Seema
C
2020-05-22
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
2023-06-17
2

Seema
B
2022-06-07
2

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