How do I use SQL/mySQL to select rows where 2 columns have multiple of the same value and 1 column has a distinct value?

Question

exTab

 PK     col1     col2     col3
 ---------------------------------
 1      val1     val4     val7   **want to return this row only
 2      val1     val4     val8
 3      val1     val4     val8
 4      val1     val5     val9
 5      val2     val5     val9
 6      val2     val5     val9
 7      val2     val6     val0
 8      val3     val6     val0

How do I use SQL (with mySQL) to return just the rows that have multiple of the same value in col1 with multiple of the same value in col2 but with a unique value in col 3?

In the table above (exTab), for instance, val1 occurs 4 times in col1, and for these 4 occurrences val4 occurs 3 times in col2, but for these 3 occurrences val7 occurs only once in col3, so I would want to return this row (row 1). Given the criteria, row 1 would be the only row I would want to return from this table.

I've tried various combinations with group by, having count > 1, distinct, where not exits, and more to no avail. This is my first post, so my apologies if I've done something incorrectly.

You should include what you have tried, otherwise your question can be interpreted as asking us to do your work instead of you. Such questions are liekely to attract down- and close votes. — Shadow
– Shadow, Commented Oct 22, 2017 at 22:28
Thanks for the advice, @Shadow. Looks like the closest I came was based on FuzzyTree's solution <stackoverflow.com/questions/23860490/…>. But I botched the syntax adding the second join, which his solution didn't require, but which you nailed. — Patrick
– Patrick, Commented Oct 23, 2017 at 0:15

Shadow · Accepted Answer · 2017-10-22 22:42:00Z

3

I would do this by combining the results of two subqueries:

In subquery 1 I would get the col1-col2 combinations which occur more than once.

In subquery 2 I would get the col1-col2-col3 combinations that occur only once.

The intersection (inner join) of these 2 subqueries would yield the record you are looking for.

select t1.*
from
    exTab t1
    inner join
        (select col1, col2 from exTab
         group by col1, col2
         having count(*)>1) t2
    inner join
        (select col1, col2, col3 from exTab
         group by col1, col2, col3
         having count(*)=1) t3 on t2.col1=t3.col1
                              and t2.col2=t3.col2
                              and t1.col1=t3.col1
                              and t1.col2=t3.col2
                              and t1.col3=t3.col3

answered Oct 22, 2017 at 22:42

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

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

1 Comment

Patrick Over a year ago

Thanks again, @Shadow. Looks like this one does the job perfectly when run against the actual 140K rows.

marc_s · Accepted Answer · 2017-10-23 04:46:55Z

0

If I've good understand the problem this SQL query might help you:

SELECT 
    SubTab.PK 
FROM 
    (SELECT  
         PK, 
         COUNT(col3) OVER (PARTITION BY col1) as col1_group,
         COUNT(col3) OVER (PARTITION BY col2) as col2_group
     FROM 
         exTab) SubTab
WHERE 
    SubTab.col1_group = 1 AND SubTab.col2_group = 1;

It will run TWO windowing aggregating functions over original Tab, and then return temporary tab and from this tab we only select this PK of rows for which col3 was unique in one group and the another too.

edited Oct 23, 2017 at 4:46

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Oct 22, 2017 at 22:34

Take_Care_

2,1641 gold badge23 silver badges25 bronze badges

2 Comments

Shadow Over a year ago

Only mysql v8.0 supports window functions and it is still in alpha state...

Patrick Over a year ago

I couldn't try this one because I'm using mySQL 5.6.14. But thanks for the knowledge, @Take_Care_.

jcaron · Accepted Answer · 2017-10-22 22:46:43Z

-1

You could try something along the lines of:

SELECT
    *
    FROM table 
    WHERE col1 IN (SELECT col1 FROM table GROUP BY 1 HAVING count(*)>1)
    AND col2 IN (SELECT col2 FROM table GROUP BY 1 HAVING count(*)>1)
    AND col3 IN (SELECT col3 FROM table GROUP BY 1 HAVING count(*)=1)

Though the performance may be terrible if your table is large.

answered Oct 22, 2017 at 22:46

jcaron

17.8k6 gold badges36 silver badges47 bronze badges

1 Comment

Patrick Over a year ago

This one doesn't return the desired rows using actual data, but thanks for jumping in and offering a solution, @jcaron.

Collectives™ on Stack Overflow

How do I use SQL/mySQL to select rows where 2 columns have multiple of the same value and 1 column has a distinct value?

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related