CODEWITHSUNDEEP
sql
imfromthenet
imfromthenet

Reputation: 11

SQL: how to select only repeated values from same column?

!!! there is a way to solve this problem using HAVING, but is there other simple way of doing it without use of HAVING ?

let's say I have a table t1 which has got two relations a and b.

-a  b
-1  2
-2  1
-3  4
-4  9
-8  5
-5  2
-6  5

how do I print only the cases from column B that are repeating (in this case: 2 and 5)?

Upvotes: 0

Views: 5849

Answers (3)

Art
Art

Reputation: 5782

In addition to already nice examples... Example with HAVING:

SELECT * FROM 
(
 SELECT col_a t1
   FROM stack_test
 ) a,
 (
 SELECT col_b t2
   FROM stack_test
  GROUP BY col_b
  HAVING Count(*) > 1
 ) b
 WHERE t1 = t2
 /

 SQL>

  T1   T2
  -------
  2     2
  5     5

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can do this with either aggregation or joins. I prefer the former, but here is one method:

select *
from t
where exists (select 1 from t t2 where t2.b = t.b and t2.a <> t.a)

This, of course, assumes that the a values differ when the b values are the same.

Upvotes: 0

Taryn
Taryn

Reputation: 247620

If you do not want a HAVING clause, then you can use a subquery:

select t1.a, t1.b
from yourtable t1
inner join
(
  select count(*) tot, b
  from yourtable
  group by b
) t2
  on t1.b = t2.b
where t2.tot > 1

See SQL Fiddle with Demo.

The subquery will be used to get the count of each b value. You then join the result to your table and filter out any records that have a count greater than 1.

This gives the result:

| A | B |
---------
| 1 | 2 |
| 8 | 5 |
| 5 | 2 |
| 6 | 5 |

Upvotes: 4

Related Questions