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.
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.
