Running MySQL 4.0.20 on a RHEL3 box.
Hi,
I'm trying to find the data in a table which is common to two or more
ids. Let's say my table looks like this...
---
| col1 | col2 |
---
| a | 1 |
| a | 2 |
| a | 3 |
| b | 4 |
| b | 2 |
| b
John
I think you are saying you want the Mode? if you are then the
following might work:
SELECT col2 as val, count(col2) AS cc
FROM my_table
GROUP BY val
ORDER BY cc DESC
LIMIT 1;
in your case this will retrun the value '2'
Regards
Ade
John Nichel wrote:
Running MySQL 4.0.20 on a RHEL3
Adrian Bruce wrote:
John
I think you are saying you want the Mode? if you are then the
following might work:
SELECT col2 as val, count(col2) AS cc
FROM my_table
GROUP BY val
ORDER BY cc DESC
LIMIT 1;
in your case this will retrun the value '2'
Regards
Ade
Thanks for the reply Ade.
Here's one way:
create table tbl (
col1 char(1),
col2 int );
insert into tbl (col1, col2)
VALUES
(a,1),
(a,2),
(a,3),
(b,4),
(b,2),
(b,7),
(c,1),
(c,2);
select col2
from tbl
group by col2
having count(col2) = 2
order by col2;
+--+
| col2 |
+--+
|1 |
|2 |
+--+
2 rows in
Dan Buettner wrote:
Here's one way:
create table tbl (
col1 char(1),
col2 int );
insert into tbl (col1, col2)
VALUES
(a,1),
(a,2),
(a,3),
(b,4),
(b,2),
(b,7),
(c,1),
(c,2);
select col2
from tbl
group by col2
having count(col2) = 2
order by col2;
+--+
| col2 |
+--+
|1 |
|2 |
