How do I get the MIN() of two fields in Postgres?

Question

Let's say I have a table like this:

name | score_a | score_b
-----+---------+--------
 Joe |   100   |   24
 Sam |    96   |  438
 Bob |    76   |  101
 ... |   ...   |  ...

I'd like to select the minimum of score_a and score_b. In other words, something like:

SELECT name, MIN(score_a, score_b)
FROM table

The results, of course, would be:

name | min
-----+-----
 Joe |  24
 Sam |  96
 Bob |  76
 ... | ...

However, when I try this in Postgres, I get, "No function matches the given name and argument types. You may need to add explicit type casts." MAX() and MIN() appear to work across rows rather than columns.

Is it possible to do what I'm attempting?

Clandestine

Answer 1

LEAST(a, b):

The GREATEST and LEAST functions select the largest or smallest value from a list of any number of expressions. The expressions must all be convertible to a common data type, which will be the type of the result (see Section 10.5 for details). NULL values in the list are ignored. The result will be NULL only if all the expressions evaluate to NULL.

Note that GREATEST and LEAST are not in the SQL standard, but are a common extension. Some other databases make them return NULL if any argument is NULL, rather than only when all are NULL...

Mountfort

For people like me who will also need MAX() of two values, it's GREATEST(a, b) :) – Wenoa

@vektor: nice way to add additional search words so that this answer can be found via Google! – Proteinase

Answer 2

Here's the link to docs for the LEAST() function in PostgreSQL:

http://www.postgresql.org/docs/current/static/functions-conditional.html#AEN15582

Medium

Answer 3

You can get the answer by putting that data into a column like this:

SELECT name, MIN(score_a, score_b) as minimum_score
FROM table

Here, we are putting the minimum value among score_a and score_b and printing the same by storing that value in a column named minimum_score.

Barracks

only min(expression) is existing. Definition from the doc: minimum value of expression across all input values with` expression` is any numeric, string, date/time, network, or enum type, or arrays of these types – Landa