通过分组获得最新的记录

Question

我有一张表格叫做“结果”

RESULTS_IDN    NAME    SUBJECT    YEAR    QUALIFIED
1              MARK    ENGLISH    1989    N
3              MARK    ENGLISH    1991    N
5              MARK    ENGLISH    1993    Y
7              MARK    ENGLISH    1995    N
2              MARK    MATH       1990    N
5              MARK    MATH       1993    N
6              MARK    MATH       1995    Y
4              MARK    SCIENCE    1991    N
9              MARK    SCIENCE    1997    Y

我需要知道最近一次考试的考生的资格状况，我该如何写一个查询(ORACLE/MSSQL)？

例如，输入

NAME,SUBJECT  OUTPUT NAME IDN SUBJECT YEAR Q
MARK,ENGLISH  OUTPUT MARK 7   ENGLISH 1995 N 
MARK SCIENCE  OUTPUT MARK 9   SCIENCE 1997 Y
MARK MATH     OUTPUT MARK 6   MATH    1995 Y

我知道一个解决问题的方法。

(SELECT NAME SUBJECT YEAR MAX(YEAR) YEAR
FROM RESULTS WHERE NAME = 'MARK' AND SUBJECT ='MATH'
GROUP BY NAME SUBJECT YEAR) LATEST 

在IDN上把上面的表加入到同一个表中，我就可以得到结果。但这是双倍的工作。无论如何，我是否可以使用“最大(年份)”来获得相应的年份，使用“有条款”或其他什么？我需要2组的数据操作，一个最新的，并相应的合格身份。

PS :当然，在DB中有这样的100名候选人的记录。

更新:根据答案2，这个问题也被归类为greatest-n-per-group问题。有趣的是知道它是DB中的一个分类问题。

Answer 1

在Oracle和Server中，您都可以使用分析/窗口函数级别()或ROW_NUMBER()来实现这一点：

select *
  from ( select a.*
              , rank() over ( partition by name, subject order by year desc ) rnk
           from ... a
                )
 where rnk = 1

name和subject的每个行返回1，ROW_NUMBER()将返回一个随机行。

仅在Oracle中，就可以使用保留提供相同的结果：

select name, subject, max(year) as year
     , max(qualified) keep (dense_rank first order by year desc) as qualified
  from ...
 group by name, subject

Answer 2

这是最大n次群一直以来重新发明的问题。

SELECT t1.*
FROM RESULTS AS t1
LEFT JOIN RESULTS AS t2
  ON t1.NAME = t2.NAME AND t1.SUBJECT = t2.SUBJECT AND t1.YEAR < t2.YEAR
WHERE t2.NAME IS NULL