CODEWITHSUNDEEP
mysqlsql
Patrick
Patrick

Reputation: 11

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?

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.

Upvotes: 0

Views: 104

Answers (3)

Take_Care_
Take_Care_

Reputation: 2144

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.

Upvotes: 0

jcaron
jcaron

Reputation: 17710

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.

Upvotes: -1

Shadow
Shadow

Reputation: 34232

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

Upvotes: 3

Related Questions