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. That's not quite what I'm trying to do
though. Allow me to expand on the table...
---------------
| col1 | col2 |
---------------
| a | 1 |
| a | 2 |
| a | 3 |
| a | 4 |
| b | 4 |
| b | 2 |
| b | 3 |
| b | 7 |
| c | 1 |
| c | 2 |
| d | 3 |
| d | 4 |
---------------
If I select col2 where col1 equals 'a', 'c' or 'd', I want it to return
nothing, because there's no value in col2 that is common to 'a', 'c',
and d. If I select col2 where col1 equals 'a', 'b' or 'd', I want it to
return 3 and 4 only, as that is common to 'a', 'b', and 'd'.
I could just query each col1 that I need, and compare the results in my
app, but I hope it can be done in just one query.
John Nichel wrote:
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 | 7 |
| c | 1 |
| c | 2 |
---------------
I'm trying to get the data in col2 that is common for
col1....basically, I want it to return '2' when I do something like:
select col2 from table where col1 = 'a' || col1 = 'b' || col1 = 'c'
(obviously that select doesn't work).
I know this probably sounds confusing, but I'm can't find a better way
to word it...guess that's why my Google searches turned up nothing I
could use. It would be much appreciated if someone could point me to
the documentation on something like this, or give me a nudge in the
right direction. TIA
--
John C. Nichel IV
Programmer/System Admin
Dot Com Holdings of Buffalo
716.856.9675
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
