How to select in the minimum date for each id in SQL (SQL Server)?

Question

How would I select a minimum (exam) date for each group (Class ID) in SQL? The values are not necessarily distinct.

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

I am looking for something like this:

ID	Date	Subject
1	2021-04-07	English
2	2020-10-02	German

Thank you.

buddemat · Accepted Answer · 2021-10-17T22:40:19.757

You can do this with a simple GROUP BY statement:

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

In case the value of subject may be different within one id and you still only want one entry per id, you can use ROW_NUMBER():

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

See this db<>fiddle.

How to select in the minimum date for each id in SQL (SQL Server)?

1 Answers1