CODEWITHSUNDEEP
sqlapache-spark-sql
Abir Chokraborty
Abir Chokraborty

Reputation: 1775

Looking for an alternative SQL statement

Given the following table with 2 columns:

c1      c2  
------------
a1   |  b1 
a1   |  b1  
a2   |  b2
a2   |  b3  
a3   |  b3

I want to return those values from column c2 where the value of c2 column appears multiple times for the same c1 value. I am doing the following SQL query to return the required result:

SELECT DISTINCT ( c2 ) AS c 
  FROM ( SELECT c1 , c2 , COUNT (*) AS rowcount 
           FROM table 
          GROUP BY c1 , c2 HAVING rowcount > 1 )

Result:

 c
---
 b1

Is there any alternative SQL statement of the above query?

Upvotes: 1

Views: 75

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271131

Based on your description, you can use:

select distinct c1
from (select t.*, count(*) over (partition by c2) as cnt
      from t
     ) t
where cnt >= 2;

Based on your sample results:

select c1
from t
group by c1
having count(*) >= 2;

And based on the revised question:

select c2
from t
group by c2
having count(*) >= 2;

Upvotes: 1

Ary Jazz
Ary Jazz

Reputation: 1676

Most answers above will work if you want all the values in c1 that appear more than once in the table (even with the same value on c2).

If you want to measure only values of c1 that may have multiple DISTINCT values on c2 you can use:

SELECT c1 
FROM table 
GROUP BY c1
HAVING COUNT(DISTINCT c2) > 1

Upvotes: 0

Gaurav
Gaurav

Reputation: 1109

Use count in having clause instead of using subquery:- 

select c1
from table
group by c1
having count(c2) > 1

Upvotes: 0

Related Questions