CODEWITHSUNDEEP
sqlpostgresql
MagikarpSama
MagikarpSama

Reputation: 323

How do I check if an array is sorted with SQL?

I have the following table

CREATE TABLE arr(
id int,
arr_r int []
);
INSERT INTO arr(arr_r ) VALUES
( ARRAY [1 ,2 ,3]) ,
( ARRAY [4 ,3]) ,
( ARRAY [7 ,6]) ,
( ARRAY [2 ,2]);

and I want to output the arrays in the table which are sorted in ascending order. An output would be 

1, 2, 3
2, 2

I tried some stuff with array_agg and an order by inside the array_agg but that did not work. How would I go about getting the desired output?

Upvotes: 2

Views: 723

Answers (2)

Belayer
Belayer

Reputation: 14886

You can eliminate duplicates by selecting unique values from unnest 

create or replace 
function sort_int_array(p_input int[]) 
 returns int[]
as
$$
  select array_agg(i order by i)
    from ( select distinct unnest(p_input) i) a;
$$
language sql
immutable;

--- Test
select sort_int_array(array[1,3,2,5,7,5,3,6,8,8,9,0])

Upvotes: 0

user330315
user330315

Reputation:

You can install the intarray extension, then you can do:

select *
from arr
where arr_r = sort(arr_r);

If you don't want to install the extension, you can create your own sort function:

create function sort(p_input int[]) 
  returns int[]
as
$$
  select array_agg(i order by i)
  from unnest(p_input) as a(i);
$$
language sql
immutable;

Online example

Upvotes: 2

Related Questions