SQL Server 2019

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

select @@version;


(No column name)


Microsoft SQL Server 2019 (RTM-CU16) (KB5011644) - 15.0.4223.1 (X64) 
	Apr 11 2022 16:24:07 
	Copyright (C) 2019 Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



create table tab (id int, date date, subject varchar(20))
insert into tab values (1,'2021-10-02','English');
insert into tab values (1,'2021-09-22','English');
insert into tab values (1,'2021-04-07','English');
insert into tab values (1,'2021-08-16','English');
insert into tab values (2,'2020-10-02','German');
insert into tab values (2,'2021-09-17','German');
insert into tab values (2,'2021-06-13','German');
insert into tab values (2,'2021-02-11','German');
insert into tab values (2,'2021-02-19','German');
9 rows affected

select * from tab;


id
date
subject


1
2021-10-02
English

1
2021-09-22
English

1
2021-04-07
English

1
2021-08-16
English

2
2020-10-02
German

2
2021-09-17
German

2
2021-06-13
German

2
2021-02-11
German

2
2021-02-19
German


  SELECT id, min(date), subject
    FROM tab
GROUP BY id, subject;


id
(No column name)
subject


1
2021-04-07
English

2
2020-10-02
German


WITH cte AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY id ORDER BY date) AS rn
   FROM tab
)
SELECT id, date, subject
FROM cte
WHERE rn = 1


id
date
subject


1
2021-04-07
English

2
2020-10-02
German

id	date	subject
1	2021-10-02	English
1	2021-09-22	English
1	2021-04-07	English
1	2021-08-16	English
2	2020-10-02	German
2	2021-09-17	German
2	2021-06-13	German
2	2021-02-11	German
2	2021-02-19	German