Re: Search for lists

Untested approach
Use array_agg on column 2 along with group by on column 1 to build check arrays and then use equals to compare with an array of your desired input values.

You should omit duplicates and order ascending both the data and the input to ensure you are matching canonical forms.

David J.


On Jun 18, 2011, at 17:51, Daron Ryan  wrote:

> Hello,
> 
> I need to search a table to find sets of rows that have a column matching itself for the whole set and another column matching row for row with a list I am going to supply. The result I should receive should be value of the column that matches itself.
> 
> For example given the following data in my table:
> 
> 3;         1
> 3;         2
> 4;         8
> 4;         9
> 4;         10
> 
> I might need to search for 1,2. This should produce the result 3. Or if I were to search for 8, 9, 10 the result should be 4. Searching for 8, 9 should produce an empty result as should 8, 9, 10, 11.
> 
> Can anyone recommend a strategy?
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general