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) ?
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
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