Showing all rows for keys with more than one row
- by Leif Neland
Table kal
id integer primary key
init char 4 indexed
job char4
id init job
--+----+------
1 | aa | job1
2 | aa | job2
3 | bb | job1
4 | cc | job3
5 | cc | job5
I want to show all rows where init has more than one row
id init job
--+----+------
1 | aa | job1
2 | aa | job2
4 | cc | job3
5 | cc | job5
I tried
select * from kal where init in (select init from kal group by init having
count(init)2);
Actually, the table has 60000 rows, and the query was count(init)<40, but it takes humongus time, phpmyadmin and my patience runs out.
Both
select init from kal group by init having count(init)2)
and
select * from kal where init in ('aa','bb','cc')
runs in "no time", less than 0.02 seconds.
I've tried different subqueries, but all takes "infinite" time, more than a few minutes; I've actually never let them finish.
Leif
