CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
matthiash
matthiash

Reputation: 3243

SQL: Find max tuple

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?

Upvotes: 1

Views: 2863

Answers (2)

bucket
bucket

Reputation: 61

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.

Upvotes: 0

klin
klin

Reputation: 121804

Use ORDER BY with LIMIT 1:

SELECT val
FROM test
ORDER BY year DESC, month DESC
LIMIT 1;

Upvotes: 2

Related Questions