HAVING – GROUP BY to get the latest record

I have a table called RESULTS like this :

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

I need to know the Qualification Status of the CANDIDATE for a SUBJECT for the LATEST exam he has written , how do I write a query for this (ORACLE/MSSQL) ?

  • Restore only data with SQL Server 2008
  • Use a Query to access column description in SQL
  • Web App to SQL Server Not Connecting ( Error Locating Server/Instance Specified)
  • Buffering question in Microsoft SQL Server
  • SQL - Executing a `CASE` satement with variable table and column names
  • Number of concurrent Sql Server connections
  • For example Input

    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
    

    I know of one way to solve this .

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

    Join the above table back on IDN to the same table and I can get the results . But this is double work . Is there anyway I can club the MAX(YEAR) and get the CORRESPONDING YEAR using HAVING CLAUSE or something ? I need 2 operations on the GROUP BY data , one Latest , and the corresponidng Qualified status .

    PS : Ofcourse there are records for 100 candidates like this in the DB .

    Update : This question is also categorized as greatest-n-per-group problem as per answer 2. Interesting to know it is a classified problem in DB .

    2 Solutions collect form web for “HAVING – GROUP BY to get the latest record”

    In both Oracle and SQL Server you can use the analytic/windowing functions RANK() or ROW_NUMBER() to achieve this:

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

    RANK() will return 1 for every row that is the newest per name and subject, ROW_NUMBER() will return a random row.

    In Oracle alone you can use KEEP to give you the same result:

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

    This is all-the-time reinvented problem of greatest-n-per-group:

    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
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.