select the most occuring element

dodo

hi i wonder if there's a way to select a most occuring element from a table with one select statement? if not, what's the most efficient way you think to do it?

here's my problem. i have a table whose structure is like this
CREATE TABLE posts (
fid smallint(6) NOT NULL default '0',
tid smallint(6) NOT NULL default '0',
pid int(10) NOT NULL auto_increment,
author varchar(40) NOT NULL default '',
message text NOT NULL,
subject varchar(100) NOT NULL default '',
dateline bigint(30) NOT NULL default '0',
icon varchar(50) default NULL,
usesig varchar(15) NOT NULL default '',
useip varchar(40) NOT NULL default '',
bbcodeoff varchar(15) NOT NULL default '',
smileyoff varchar(15) NOT NULL default '',
PRIMARY KEY (pid),
KEY fid (fid),
KEY tid (tid),
KEY dateline (dateline)
) TYPE=MyISAM;

i wish to select the most occuring fid for a specific author. something like

SELECT fid FROM posts FROM author='blah' ORDER BY count(fid) DESC LIMIT 1

i know the above is NOT WORKING.

tips are welcome! thanks!

cgraz

it's been a while since I've used count, but give this a try

SELECT COUNT(fid) AS most FROM stock ORDER BY most DESC LIMIT 1

Cgraz

dodo

WAIT
first i thought it worked but it didn't. it didn't spit out error but it gave the incorrect results. it's giving me 0 which i know it's incorrect.

aw thanks. i was close. can i join that with another statement where it selects the actual name of the fid from another table called "forums" ?

so instead of doing anther select statement with
SELECT name FROM $table_forums WHERE fid='$most_active_in[fid]'

can i put them together?

cgraz

yeah, i'm not sure how though 🙁 maybe someone else can jump in here and help out.

Cgraz

dodo

maybe i was bit misleading. i don't really want to count(fid) i want to find the most occuring value of fid 🙁 maybe it's impossible to do with select. i might be better of just doing it with array manipulations.

xblue

before doing that, I'd try

SELECT fid, COUNT(fid) AS most FROM stock WHERE author='blah' GROUP BY fid ORDER BY most DESC LIMIT 0,1

dodo

hey it worked!!! awesome. but i was actually doing the array manipulation last nite and i found out that it will be more useful if i can the first three most active forums. so i tried modifying your code to
LIMIT 0,3

but it's giving me the same anwer that is the most active one. anyway to fix that?

nnichols

SELECT p.fid, f.name, COUNT(p.fid) AS occurrences
FROM posts p, forums f
WHERE p.author='blah'
AND p.fid=f.fid
GROUP BY p.fid
ORDER BY occurrences DESC
LIMIT 3

dodo

your code is beautiful. i learned quite a bit from it. unfortunately it still only gives the most occuring one instead of the top 3. thank you for helping!

nnichols

Can you post a small (zipped) structure/data dump from the 2 tables. The query that 'xblue' wrote, that I elaborated on, should do the trick.

nnichols

I created the following 2 tables to test the query -

+-----+--------------+
| fid | name         |
+-----+--------------+
|   1 | General Help |
|   2 | PHP          |
|   3 | MySQL        |
|   4 | PostgreSQL   |
|   5 | Oracle       |
|   6 | ASP          |
+-----+--------------+

+------+------+-----+--------+---------+
| fid  | tid  | pid | author | message |
+------+------+-----+--------+---------+
|    1 |    0 |   1 | me     | NULL    |
|    1 |    0 |   2 | me     | NULL    |
|    2 |    0 |   3 | me     | NULL    |
|    2 |    0 |   4 | me     | NULL    |
|    2 |    0 |   5 | me     | NULL    |
|    2 |    0 |   6 | me     | NULL    |
|    4 |    0 |   7 | me     | NULL    |
|    4 |    0 |   8 | me     | NULL    |
|    4 |    0 |   9 | me     | NULL    |
|    4 |    0 |  10 | me     | NULL    |
|    4 |    0 |  11 | me     | NULL    |
|    4 |    0 |  12 | me     | NULL    |
|    5 |    0 |  13 | me     | NULL    |
|    5 |    0 |  14 | me     | NULL    |
|    5 |    0 |  15 | me     | NULL    |
|    5 |    0 |  16 | me     | NULL    |
|    5 |    0 |  17 | me     | NULL    |
|    5 |    0 |  18 | me     | NULL    |
|    5 |    0 |  19 | me     | NULL    |
|    5 |    0 |  20 | me     | NULL    |
+------+------+-----+--------+---------+

and when I ran the query it returned the following -

+------+------------+-------------+
| fid  | name       | occurrences |
+------+------------+-------------+
|    5 | Oracle     |           8 |
|    4 | PostgreSQL |           6 |
|    2 | PHP        |           4 |
+------+------------+-------------+

exactly as it should - so I don't understand where you are going wrong.

dodo

ha, you are right. i didn't do a while loop when i fetch the results. thanks!