My SQL Query Question

[Samman]

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.

--

Samman
Rip it to reply

[windandwaves]

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