How to get the minimum value from an array-CodePudding

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]);

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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;

CodePudding user response：

You can use CROSS JOIN UNNEST, then extract the minimum value:

SELECT letter, MIN(val)
FROM cte
CROSS JOIN UNNEST(arr) AS arr(val)
GROUP BY letter

Check the demo here.