Postgres, find the min value of array column and get its position

Question

Let us assume the following table: test

ids | l_ids      |  distance
-----------------+-----------
 53 | {150,40}   | {1.235, 2.265}
 22 | {20,520}   | {0.158, 0.568}

The positions of the two arrays (l_ids, distance) are dependent, meaning that 150 corresponds to 1.235, 40 corresponds to 2.265 and so on.

I want to get the min distance and the respective l_id. Therefore the result should be like this:

   ids | l_ids      |  distance
   ----------------+-----------
    53 |  150       |  1.235
    22 |   20       |  0.158

By running this:

select l_ids, min(dist) as min_distance 
from test, unnest(test.distance) dist
group by 1;

the result is:

     l_ids      |  dist
    ------------+-----------
     {150,40}   |  1.235
     {20,520}   |  0.158

I want to get the position of the minimum value in the array of distance in order to get the respective id from the array of l_ids.

Any guideline? Thank you in advance

in distance array, values are unique? or may be {1.235, 1.235, 2.265} also happens? — Oto Shavadze
– Oto Shavadze, Commented Feb 15, 2017 at 15:22
The length of the arrays may be greater than 2, but always equal to each other. — Dino C
– Dino C, Commented Feb 15, 2017 at 15:24
can you have in l_ids for example: {150, 70, 40} and in distance these values: {1.235, 1.235, 2.265} (1.235 repeates) — Oto Shavadze
– Oto Shavadze, Commented Feb 15, 2017 at 15:26

Laurenz Albe · Accepted Answer · 2017-02-15 15:10:24Z

2

SELECT DISTINCT ON (ctid) l_ids, distance
FROM (SELECT ctid,
             unnest(l_ids) AS l_ids,
             unnest(distance) AS distance
      FROM test
     ) q
ORDER BY ctid, distance;

 l_ids | distance
-------+----------
   150 |    1.235
    20 |    0.158
(2 rows)

answered Feb 15, 2017 at 15:10

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dino C Over a year ago

Thanks for the reply. It works. Check please my edit in the question.

Laurenz Albe Over a year ago

Use ids instead of ctid.

Oto Shavadze · Accepted Answer · 2017-02-15 15:41:54Z

1

If ids are unique identiter, and you don't have repeated values in distance array, this should work:

SELECT a.* from  
(SELECT ids, UNNEST(l_ids) AS l_ids, UNNEST (distance) AS distance  FROM test) a
INNER JOIN (SELECT ids, MIN(distance) as mind FROM (
        SELECT ids,  UNNEST (distance) AS distance  FROM test 
    ) t
GROUP BY ids ) t
ON
a.ids = t.ids and a.distance = t.mind

answered Feb 15, 2017 at 15:41

Oto Shavadze

43.3k56 gold badges169 silver badges248 bronze badges

3 Comments

Dino C Over a year ago

Thanks, it s really closed to the solution. However it returns 12 rows more. I checked for duplicates. The ids are unique and there is no repeated value in distance.

Dino C Over a year ago

As I said it s really closed. The query returns 10 duplicates of ids. However each duplicate has the same min distance, which is not critical for my further work. Thanks again for the assistance

Oto Shavadze Over a year ago

So, its seems that you have duplicates in distance array, this causes more rows in result. glad if this was helpful for you.

pozs · Accepted Answer · 2017-02-15 15:46:43Z

1

From 9.4, you can use UNNEST()'s special ROWS FROM()-like syntax:

select   distinct on (ids) ids, l_id, dist min_distance
from     test, unnest(l_ids, test.distance) d(l_id, dist)
order by ids, dist

Otherwise, this is typical greatest-n-per-group query.

answered Feb 15, 2017 at 15:46

pozs

36.6k5 gold badges61 silver badges66 bronze badges

1 Comment

klin Over a year ago

+ for conciseness.

Collectives™ on Stack Overflow

Postgres, find the min value of array column and get its position

3 Answers 3

2 Comments

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related