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

sql postgresql min
mike picture mike · Nov 25, 2008 · Viewed 72.6k times · Source

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?

Answer

cagcowboy picture cagcowboy · Nov 25, 2008

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...

Related questions

Postgres - find min of array

Suppose I have a table like this: link_ids | length ------------+----------- {1,4} | {1,2} {2,5} | {0,1} How can I find the min length for each link_ids? So the final output looks something like: link_ids | length ------------+----------- {1,4} | 1 {2,5} | 0

Read More
Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How do I (or can I) SELECT DISTINCT on multiple columns?

I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The sales …

Read More