!!! 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)?
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 |
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
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.
SQL Server?MySQL?Oracle?DB2? etc..HAVINGis for exactly this purpose - what's wrong with using it?HAVING? And why do you not want to useHAVING?