CODEWITHSUNDEEP
postgresql
Hugh_Kelley
Hugh_Kelley

Reputation: 1040

How to get the minimum value from an array

If I have an array of ints, how can I get the min value out of the array?

The min() and least() functions won't work directly on the array.

for example if I have an array like below:

select 'a', array[1,2,3];

I want 'a', 1 as the results of my query.

I've tried select 'a', min(array[1,2,3]); and select 'a', least(array[1,2,3]);

Upvotes: 1

Views: 825

Answers (4)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657052

LEAST() is only good for a small, known number of array elements, allowing us to spell it out like this:

SELECT LEAST(arr[1], arr[2], arr[3]);

Else look to the answers of Adrian and a_horse.

Upvotes: 1

user330315
user330315

Reputation:

You can install the intarray extension to sort the array and then pick the first element:

select (sort(array[3,2,1], 'asc'))[1]

Alternatively you can write such a function:

create function array_min(p_input int[])
  returns int
as
$$
   select *
   from unnest(p_input) as x(v)
   order by x.v nulls last
   limit 1
$$
language sql
immutable;

For the corresponding implementation of array_max() you need to use an order by ... desc

Upvotes: 1

Adrian Klaver
Adrian Klaver

Reputation: 19665

select 'a', min(t) from unnest(array[1,2,3]) as t;
 ?column? | min 
----------+-----
 a        |   1

Upvotes: 2

Hugh_Kelley
Hugh_Kelley

Reputation: 1040

we can unnest the array, then group by the unique id, and use the min() function on that query to get the min for each unique row.

run the following queries one by one to see how it works.

SELECT
    'a' AS id,
    array [1,2,3] AS the_array;

SELECT
    id,
    unnest(the_array) unnested_array
FROM
    (
        SELECT
            'a' id,
            array [1,2,3] AS the_array
    ) AS src;

SELECT
    id,
    min(unnested_array) AS min
FROM
    (
        SELECT
            id,
            unnest(the_array) unnested_array
        FROM
            (
                SELECT
                    'a' id,
                    array [1,2,3] AS the_array
            ) AS src
    ) AS subquery
GROUP BY
    id;

Upvotes: 0

Related Questions