current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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
share|improve this question
    
Why are your columns arrays? That looks like a really bad schema design. –  Falmarri Feb 10 at 0:05
    
To enlarge on @Falmarri 's comment - there is a set of formal rules (called normal forms) which describe how the schema of relational databases should look like in order to prevent lots of problems - it's considered sensible to conform to at least the first three - your schema doesn't conform to the first one since the cells of your table don't store atomic values. You should use tables to store lists. –  lared Feb 10 at 0:08
    
I'm familiar with normal forms. This is the result of a recursive query, that I'd like to further process. –  dal102 Feb 10 at 0:14
    
You should write your recursive query so that it gives you rows instead of arrays. –  Falmarri Feb 10 at 0:17
    
Your version of Postgres is essential to this question. Also: can columns be NULL? Can the array be empty? Is link_ids unique? Are arrays in ascending order like your example suggests? If you are working with an actual table, post the table definition. Else, it would be better to post your recursive query: there might be a better solution to begin with. (Plus table definitions for underlying tables.) –  Erwin Brandstetter Feb 10 at 0:37

3 Answers 3

up vote 2 down vote accepted

Assuming a table like this:

CREATE TABLE t (
  link_ids int[] PRIMARY KEY     -- which is odd for a PK
, length int[]
, CHECK (length <> '{}'::int[])  -- rules out null and empty in length
);

This query works in Postgres 9.3+

SELECT link_ids, min(len) As min_length
FROM   t, unnest(t.length) len  -- implicit LATERAL join
GROUP  BY 1;

Or you create a little function (Postgres 8.4+):

CREATE OR REPLACE FUNCTION arr_min(anyarray) RETURNS anyelement AS
'SELECT min(i) FROM unnest($1) i' LANGUAGE sql IMMUTABLE;

Then:

SELECT link_ids, arr_min(length) AS min_length FROM t;

Or, to make this fast, as long as we are dealing with integer arrays of trivial length, you could use the additional module intarray and use the built-in sort() function (Postgres 8.3+):

SELECT link_ids, (sort(length))[1] AS min_length FROM t;
share|improve this answer
up vote 1 down vote

Assuming that the table name is t and each value of link_ids is unique.

select link_ids, min(len)
from (select link_ids, unnest(length) as len from t) as t
group by link_ids;

 link_ids | min
----------+-----
 {2,5}    |   0
 {1,4}    |   1
share|improve this answer
up vote 0 down vote

(I'm gonna assume link_ids can have doubles and since there is no id column we're gonna improvise).

WITH r AS
(SELECT row_number() OVER() as id,
       link_ids,
       length from Table1)
SELECT DISTINCT ON (id) link_ids,
       unnest(length) 
FROM r 
ORDER BY id, length;

fiddle

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.