How to use SQL group to filter rows with minimum date value?

Question

I have the following table:

STUDENT_CODE	REGION_CODE	COURSE_CODE	SUBMIT_DATE
46588225	N	AAA00004	2017-03-16 06:08:40.031000
85788225	N	AAA00004	2017-03-24 12:14:29.493000
85789271	N	AAA00004	2017-06-24 12:14:29.493000
884444	N	B0006	2017-10-05 00:33:08.627000
2558855	N	B0006	2019-08-02 02:47:22.996000

I want to achieve the result which selects only student code having min value of submit_date of region_code and course_code. ie

STUDENT_CODE	REGION_CODE	COURSE_CODE	SUBMIT_DATE
46588225	N	AAA00004	2017-03-16 06:08:40.031000
884444	N	B0006	2017-10-05 00:33:08.627000

how can I achieve this using group by and FETCH FIRST 1 ROW ONLY? I have done as follows but it is not working.

select a.STUDENT_CODE,a.REGION_CODE,a.COURSE_CODE,min(a.SUBMIT_DATE) from STUDENT as a where a.REGION_CODE='N' and a.COURSE_CODE IN ('AAA00004','B0006') group by a.STUDENT_CODE, a.REGION_CODE, a.COURSE_CODE order by a.COURSE_CODE asc FETCH FIRST 1 ROW ONLY;

Answer 1

I would recommend using row_number() for this:

select s.*
from (select s.*,
             row_number() over (partition by region_code, course_code order by submit_date desc) as seqnum
      from student s
      where s.REGION_CODE = 'N' and
            s.COURSE_CODE IN ('AAA00004', 'B0006')
     ) s
where seqnum = 1;