sql - 如何使用 SQL 组过滤具有最小日期值的行？

Question

我有下表：

STUDENT_CODE	REGION_CODE	课程代码	提交_DATE
46588225	ñ	AAA00004	2017-03-16 06:08:40.031000
85788225	ñ	AAA00004	2017-03-24 12:14:29.493000
85789271	ñ	AAA00004	2017-06-24 12:14:29.493000
884444	ñ	B0006	2017-10-05 00:33:08.627000
2558855	ñ	B0006	2019-08-02 02:47:22.996000

我想获得仅选择具有 region_code 和 course_code 的 submit_date 最小值的学生代码的结果。IE

STUDENT_CODE	REGION_CODE	课程代码	提交_DATE
46588225	ñ	AAA00004	2017-03-16 06:08:40.031000
884444	ñ	B0006	2017-10-05 00:33:08.627000

如何使用 group by 和 FETCH FIRST 1 ROW 来实现这一点？我做了如下，但它不工作。

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;

标签： sqlgroup-bylimitdb2-400

Answer 1

我建议使用row_number()这个：

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;