SQL: Find max tuple

Question

In PostgreSQL, I have a table with the following values:

year | month | val
-----|-------|------
2014 |    1  |   x
2014 |   12  |   y
2015 |    1  |   z

There is a hierarchical relationship between year and month, and I want to find val of the maximum tuple (year,month). Thus in this example I want to return val 'z', as 2015 is the max year and 1 is the max month within that year. This can be achieved with an inner query:

SELECT val
FROM (
    SELECT val, row_number() OVER (ORDER BY YEAR DESC, MONTH DESC) AS r
    FROM test
) foo
WHERE r=1

See http://sqlfiddle.com/#!15/17fb6/16 But is there a simpler and neater way of doing this?

Answer 1

You can get away with not having to sort everything (which is what happens if you use ORDER) by doing a self semi-join like:

SELECT year, month, val FROM test t1 WHERE NOT EXISTS (
  SELECT 1 FROM test t2 
  WHERE (t2.year = t1.year AND t2.month > t1.month) OR 
         t2.year > t1.year)

Note that this gives you all max valued tuples if there are ties. You can throw in an OR (t1.year = t2.year AND t1.month = t2.month AND t2.ctid > t1.ctid) in the exists clause if you want a single row.