Postgres Get Max or Min of Tuple/Record-CodePudding

How do I get a min or max of a record in postgres?

For example:

SELECT 
   max(num), max(letter)
FROM (
   VALUES 
      (1, 'c'), 
      (3, 'a'),
      (3, 'b')
) AS t (num,letter);

Will give me (3, 'c').

The problem is that you can't do max(ROW(num, letter)).

However I'd like a function that finds the max value of a tuple, which would be in python

>>> max([
   (1, 'c'),
   (3, 'a'),
   (3, 'b'),
])
(3, 'b')

Is there a way to get this in Postgres easily?

CodePudding user response：

You could use a LIMIT query here, with two sort levels for the two columns:

SELECT num, letter
FROM yourTable
ORDER BY num DESC, letter DESC
LIMIT 1;

In the event that there could be two or more records "tied" as being first, then use RANK:

WITH cte AS (
    SELECT *, RANK() OVER (ORDER BY num DESC, letter DESC) rnk
    FROM yourTable
)

SELECT num, letter
FROM cte
WHERE rnk = 1;

CodePudding user response：

You could use NOT EXISTS(...), and compare complete tuples:

WITH stuff (num,letter) AS(
   VALUES
      (1, 'c'),
      (3, 'a'),
      (3, 'b')
)
SELECT num,letter
FROM stuff t1
WHERE NOT EXISTS (
        SELECT * FROM stuff nx
        WHERE (nx.num, nx.letter) > (t1.num, t1.letter)
        );

Simplified:

WITH stuff (num,letter) AS(
   VALUES
      (1, 'c'),
      (3, 'a'),
      (3, 'b')
)
SELECT num,letter
FROM stuff t1
WHERE NOT EXISTS (
        SELECT * FROM stuff nx
        WHERE (nx.*) > (t1.*)
        );

Simplified:

WITH stuff (num,letter) AS(
   VALUES
      (1, 'c'),
      (3, 'a'),
      (3, 'b')
)
SELECT num,letter
FROM stuff t1
WHERE NOT EXISTS (
        SELECT * FROM stuff nx
        WHERE (nx) > (t1)
        );