Posted by windandwaves on 10/12/05 01:59

Samman wrote:
> I have a table that contains 6 fields of integers. The table
> currently has 80 records. I would like to find out the five or six
> most common numbers in the entire table (all fields, all records). Is
> this something I could do with a query, or would I be better off
> approaching this programmatically (PHP)?
>
> Thanks.

This is what I would do (NOT TESTED):

1. create a temp table (mytemp) with one field (tempfield as integer)
2. for this example, your table is called mytable and the fields are field1,
field2, field3, field4, field5, and field6

sql = "delete from mytemp;"
if(mysql_query($sql)) {
for($i = 1; $i < 7; $i++) {
$sql = "select `field".$i."` FROM mytable;";
$query = mysql_query($sql);
while($row = mysql_fetch_row($query)) {
$sql2 = 'INSERT INTO mytemp (tempfield) value("'.$row[0].'");';
if(!mysql_query($sql2)) {die("could not add number");}
}
}
}
$sql = "select count(tempfield) a, tempfield from mytemp order by a desc;"
echo mysql_query($sql);

- Nicolaas

[Back to original message]