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 1 down vote favorite

I have a table that looks like this:

val | fkey | num
------------------
1   |  1   | 1
1   |  2   | 1  
1   |  3   | 1  
2   |  3   | 1

What I would like to do is return a set of rows in which values are grouped by 'val', with an array of fkeys, but only where the array of fkeys is greater than 1. So, in the above example, the return would look something like:

1 | [1,2,3]

I have the following query aggregates the arrays:

SELECT val, array_agg(fkey)
FROM mytable
GROUP BY val;

But this returns something like:

1 | [1,2,3]
2 | [3]

What would be the best way of doing this? I guess one possibility would be to use my existing query as a subquery, and do a sum / count on that, but that seems inefficient. Any feedback would really help!

share|improve this question

2 Answers 2

up vote 2 down vote accepted

Use Having clause to filter the groups which is having more than fkey

SELECT val, array_agg(fkey)
FROM mytable
GROUP BY val
Having Count(fkey) > 1
share|improve this answer
up vote 1 down vote

Using the HAVING clause as @Fireblade pointed out is probably more efficient, but you can also leverage subqueries:

SQLFiddle: Subquery

SELECT * FROM (
   select   val, array_agg(fkey) fkeys
   from     mytable
   group by val
) array_creation
WHERE array_length(fkeys,1) > 1

You could also use the array_length function in the HAVING clause, but again, @Fireblade has used count(), which should be more efficient. Still:

SQLFiddle: Having Clause

SELECT   val, array_agg(fkey) fkeys
FROM     mytable
GROUP BY val
HAVING   array_length(array_agg(fkey),1) > 1

This isn't a total loss, though. Using the array_length in the having can be useful if you want a distinct list of fkeys:

SELECT val, array_agg(DISTINCT fkey) fkeys

There may still be other ways, but this method is more descriptive, which may allow your SQL to be easier to understand when you come back to it, years from now.

share|improve this answer
    
Unfortunately I could only pick one answer but this is actually super helpful as well. –  Clicquot the Dog 1 hour ago
    
His was the obvious better choice ;) Nice job and good luck. –  vol7ron 1 hour ago

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.