PostgreSQL – How to Perform MAX Over a Pair of Columns

aggregatepostgresql

I would like to execute the following:

SELECT person, MAX( (date, priority) ) FROM table GROUP BY person;

It would return a person, date and priority row for each distinct value in the person column. The date and priority are chosen such that the date value is maximised, and the maximum priority that occurs on that date is chosen.

As an example, running the query on this table

person |     date     | priority
---------------------------------
   1   | '2014-01-01' |    10
   1   | '2014-01-02' |     2
   1   | '2014-01-02' |     3

should result in

person |     date     | priority
---------------------------------
   1   | '2014-01-02' |     3

Postgres complains about this particular attempt:

ERROR:  function max(record) does not exist
HINT:  No function matches the given name and argument types.
You might need to add explicit type casts.

Is there a way that I can achieve this? Anything that works with Postgres will be acceptable but if a solution is more standard then so much the better.

Best Answer

Does this fits your needs?

SELECT DISTINCT ON (person)
       person, date, priority
  FROM table
 ORDER BY person, date DESC, priority DESC;
Related Question