[SOLVED] top ten listing

[rugzo]

Hi All,

i have a table like below -->

 

id        subject

___      ______

0        pc

1        network

2        account

3        network

4      email

5        pc

6        network

...      ...

 

there are over 20000 entries. I want to count each subject and want to list the top ten subjects in one query. Result should look like below -->

 

topten

1- network (3 times)

2- pc (2)

3- account (1)

...

 

Is this possible in one qeury or is there an sql command for this like rank?

 

thanks

[MadTechie]

Try this

SELECT subject, COUNT(subject) AS Occurrences
FROM table
GROUP BY subject
ORDER BY Occurrences DESC
LIMIT 0 , 10

change table to table name

 

 

echo $row['subject']." (".$row['Occurrences']." times)";

 

EDIT: oops forgot the order (now added)

[rugzo]

$query = mysql_query("SELECT subject, COUNT(subject) AS Occurrences FROM nsnemailgr GROUP BY subject LIMIT 0 , 10") ;

while ($topten = mysql_fetch_arrray($query)){

  echo $topten['subject'] ;

}

 

i get an error when i do this --->

Fatal error: Call to undefined function mysql_fetch_arrray() in C:\xampp\htdocs\emt.php on line 6

 

[rugzo]

Ok i found the problem i typed arrray wrong

[MadTechie]

arrray ?

 

EDIT: ok you found it

[MadTechie]

Code should look like this

<?php
$query = mysql_query("SELECT subject, COUNT(subject) AS Occurrences FROM nsnemailgr GROUP BY subject ORDER BY Occurrences DESC LIMIT 0 , 10") ;
while ($topten = mysql_fetch_array($query)){
  echo $topten['subject']." (".$topten['Occurrences']." times)<br>\n";
}
?>

[rugzo]

Thank you very much