Select Group of Rows That Match All Items in a List

Select group of rows that match all items in a list

How about this?

SELECT carname
FROM PassedTest
GROUP BY carname
HAVING COUNT(DISTINCT testtype) = 4

You can also use it as an inner statement for taking info from the cars table:

SELECT *
FROM cars
WHERE carname IN (
    SELECT carname
    FROM PassedTest
    GROUP BY carname
    HAVING COUNT(DISTINCT testtype) = 4
)

select group of rows that matches all its associated items to a list

You can use aggregation, and a having clause that ensures that none of the names in the group does not belong to the list:

select id
from mytable
group by id
having max(name not in ('Lucy', 'Frank', 'Jane', 'Robert', 'Brandon')) = 0

Return rows only if matches all list values

You can do this with group by and having:

select cl.custid
from cust_lang cl join
     languages l
     on cl.langid = l.id
where l.language in ('English', 'Spanish', 'Portuguese')
group by cl.custid
having count(*) = 3;

If, for example, you only wanted to check for two languages, then you need only change you WHERE ... IN and HAVING conditions, e.g.:

where l.language in ('English', 'Spanish')

and

having count(*) = 2

SQL Select a group when attributes match at least a list of values

In the where criteria filter on the list of values you would like to see, group by id and in the having clause filter on those ids which have 3 matching rows.

select id from table1
where value in ('A', 'B', 'C') --you can use a result of another query here
group by id
having count(*)=3

If you can have the same id - value pair more than once, then you need to slightly alter the having clause: having count(distinct value)=3

If you want to make it completely dynamic based on a subquery, then:

select id, min(valcount) as minvalcount from table1
cross join (select count(*) as valcount  from table1 where id=2) as t1
where value in (select value from table1 where id=2) --you can use a result of another query here
group by id
having count(*)=minvalcount

Select from table rows that match all items in a where in list

You a filtering by list, and in just checks it for membership of every input value. So it doesnt't matter what is the ordinal position of the value in list, it's intended for filtering only. To distinguish two different instances of the same value you need to turn this list into table or do some other transformation that holds the order information.

One way can be a table value constructor:

with flt as (
  select
    val,
    row_number() over(partition by val order by val) as rn /*To distinguish instances of the value*/
  from(values (90),(120),(30),(90)) as t(val)
)
, base as (
  select
    f.*,
    row_number() over(partition by somefield order by id) as rn
  from ForgeRock as f
  where somefield in (select flt.val from flt) /*To restrict input before row number*/
)
select b.*
from base as b
  join flt
    /*Match value AND repetition*/
    on b.somefield = flt.val
      and b.rn = flt.rn

id	somefield	rn
4	30	1
1	90	1
2	90	2
3	120	1

How to select value that matches all values in a list?

One method is:

where status in (0, 2)

But I suspect you want both values for the id. In that case, one method uses exists:

select t.*
from t
where status in (0, 2) and
      exists (select 1
              from t t2
              where t2.id = t.id and
                    t2.status in (0, 2) and
                    t2.status <> t.status
             );

If you just want the ids, then aggregation is easy:

select id
from t
where status in (0, 2)
group by id
having count(*) = 2;

This can be incorporated in a query to get the original rows using in, exists, or join. Or window functions:

select t.*
from (select t.*,
             count(*) filter (where status in (0, 2)) over (partition by id) as cnt
      from t
     ) t
where cnt = 2;

Python Pandas Multiindexing select rows that match all values in a list

This is an alternative to @jezrael's approach, where we group on the boolean values from isin and country:

In [38]: (df.groupby([df.index.isin([1,3,4], level='Folder'),
                      df.index.get_level_values('Country')])
            .filter(lambda x: len(x)==3)
           )
Out[38]: 
                Data
Country Folder      
Canada  3         34
        4         47
        1         18

Take advantage of the fact that you have three numbers in the list, so if it matches all, then it should be 3.

To get all values, you could chunk the steps:

mapping = df.index.isin([1,3,4], level = 'Folder')

filtered = (pd.Series(mapping)
            .groupby(df.index.get_level_values('Country'))
            .transform(lambda x: sum(x)>=3)
            )

In [61]: df.loc[filtered.array]
Out[61]: 
                Data
Country Folder      
Canada  2         24
        3         34
        4         47
        5         55
        1         18

Select records that match at least all items in a list

Query

Included are two variations of the same query for either database.

---

MySQL

DBFiddle

SELECT main.* 
FROM   main
LEFT JOIN (
  SELECT name, json_arrayagg(type) as type
  FROM main
  GROUP BY name
) AS main_agg USING(name)

WHERE EXISTS (
  SELECT 1
  FROM (
    select json_arrayagg(type) as type
    from query 
    group by name
  ) AS query_agg
  WHERE JSON_CONTAINS(main_agg.type, query_agg.type)
)

• groups types by name
• uses the JSON_CONTAINS function to compare the table to the query

---

Postgres

SQLFiddle

WITH main_agg AS
(
  SELECT name, array_agg(type) "type"
  FROM main
  GROUP BY name
)

SELECT main.* 
FROM   main
JOIN   main_agg USING(name)
WHERE EXISTS (
  SELECT 1
  FROM (select array_agg(type) "type" from query group by name) query_agg
  WHERE main_agg."type" @> query_agg."type"
)

• groups types by name
• utilizes the Array @> (contains operator) to compare to the query

---

Setup

(Works for MySQL or PostgreSQL)

CREATE TABLE main
    (ID int, Name varchar(6), Type varchar(1), Value int)
;

INSERT INTO main
    (ID, Name, Type, Value)
VALUES
    (1, 'First', 'A', 10),
    (2, 'First', 'B', 20),
    (3, 'First', 'C', 30),
    (4, 'First', 'D', 40),
    (5, 'Second', 'A', 10),
    (6, 'Second', 'B', 20)
;


CREATE TABLE query
    (ID int, Name varchar(5), Type varchar(1), Value int)
;

INSERT INTO query
    (ID, Name, Type, Value)
VALUES
    (1, 'Third', 'A', 10),
    (2, 'Third', 'B', 20),
    (3, 'Third', 'C', 30)
;

Select every rows that contains all elements of group

You can use group by and having:

select name
from t
group by name
having count(*) = (select count(distinct type) from t);

This assumes that the name/type rows are not repeated in the table.

Edit:

If you just want to check for A/B/C, then:

select name
from t
where type in ('A', 'B', 'C')
group by name
having count(*) = 3;

Or:

having count(distinct type) = 3

if the table has duplicates.

Select rows having ALL of the IN list references

In the end, as kindly pointed out by @Thomas, there was already solution for my problem here - Finding items with a set containing all elements of a given set with jpql

I decided to share exact solution code for my problem here, maybe it will help somebody:

@Query("SELECT c FROM Contact c JOIN c.targetGroups tg " +
            "WHERE (tg.id IN :targetGroups)" +
            " GROUP BY c.id HAVING count(c.id) = :groupsCount")
    Page<ContactView> customFilterWithTargetGroups (@Param("targetGroups") Set<Integer> targetGroups, @Param("groupsCount") long groupsCount, Pageable pageable);

---

Related Topics

Listagg in Oracle to Return Distinct Values

Strange Duplicate Behavior from Group_Concat of Two Left Joins of Group_Bys

Difference Between Exists and in in Sql

MySQL Foreign Key Constraint Is Incorrectly Formed Error

Reset Identity Seed After Deleting Records in SQL Server

How to Spool to a CSV Formatted File Using Sqlplus

Subqueries VS Joins

Add a Column With a Default Value to an Existing Table in SQL Server

Equals(=) Vs. Like

SQL Server: How to Insert into Two Tables At the Same Time

How to List the Tables in a Sqlite Database File That Was Opened With Attach

Count the Occurrences of Distinct Values

SQL Server Process Queue Race Condition

Get Records With Max Value For Each Group of Grouped SQL Results

Solutions For Insert or Update on SQL Server

Why Is Select * Considered Harmful

Two SQL Left Joins Produce Incorrect Result

SQL - Using Alias in Group By

---

Leave a reply

Submit