Find max over multiple columns

Question

I am trying to query a list of meetings from the most recent semester, where semester is determined by two fields (year, semester). Here's a basic outline of the schema:

Otherfields    Year    Semester
meeting1       2014    1
meeting2       2014    1
meeting3       2013    2
... etc ...

As the max should be considered for the Year first, and then the Semester, my results should look like this:

Otherfields    Year    Semester
meeting1       2014    1
meeting2       2014    1

Unfortunately simply using the MAX() function on each column separately will try to find Year=2014, Semester=2, which is incorrect. I tried a couple approaches using nested subqueries and inner joins but couldn't quite get something to work. What is the most straightforward approach to solving this?

Answer 1

Using a window function:

SELECT Year, Semester, RANK() OVER(ORDER BY Year DESC, Semester DESC) R
FROM your_table;

R will be a column containing the "rank" of the couple (Year, Semester). You can then use this column as a filter, for instance :

WITH TT AS (
  SELECT Year, Semester, RANK() OVER(ORDER BY Year DESC, Semester DESC) R
  FROM your_table
)
SELECT ...
FROM TT
WHERE R = 1;

If you don't want gaps between ranks, you can use dense_rank instead of rank.

This answer assumes you use a RDBMS who is advanced enough to offer window functions (i.e. not MySQL)

Answer 2

I wouldn't be surprised if there's a more effecient way to do this (and avoid the duplicate subquery), but this will get you the answer you want:

SELECT * FROM table WHERE Year = 
    (SELECT MAX(Year) FROM table)
AND Semester =
    (SELECT MAX(Semester) FROM table WHERE Year =
        (SELECT MAX(Year) FROM table))