On 11 September 2010 07:47, Ron Piggott <ron.piggott@xxxxxxxxxxxxxxxxxx> wrote: > > I wrote the query below to determine the 10 most popular words used: > > SELECT COUNT( `bible_concordance_usage`.`reference` ) AS word_usage, > `bible_concordance_words`.`reference` , `bible_concordance_words`.`word` > FROM `bible_concordance_usage` > INNER JOIN `bible_concordance_words` ON > `bible_concordance_usage`.`bible_concordance_words_reference` = > `bible_concordance_words`.`reference` > GROUP BY `bible_concordance_usage`.`bible_concordance_words_reference` > ORDER BY word_usage DESC, `bible_concordance_words`.`word` ASC, > `bible_concordance_usage`.`date_accessed` DESC > LIMIT 10 > > What I don't like about the results is that if 8 words have been used 5 > times then the remaining 2 words the query chooses are from words used 4 > times. The results are in alphabetical order A to Z for the words used 5 > times and back to A to Z for words used 4 times. > > My question: is there a way to make my query above into a "sub query" and > have a main query order the results of the sub query "ORDER BY words ASC" > so all the words displayed are in alphabetical order? > > Ron > > Ron > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > Can't you just swap the order of the first 2 columns in the ORDER BY clause? e.g. ORDER BY Name, Age will list all the names alphabetically and where there are more than 1 occurrence of a name in the result set, these would be ordered by age. versus. ORDER BY Age, Name will list all the babies in alphabetical order, followed by the toddlers, children, teenagers, adults, grannies and granddads. So, ORDER BY `bible_concordance_words`.`word` ASC, word_usage DESC,`bible_concordance_usage`.`date_accessed` DESC And as you are grouping by `bible_concordance_words`.`word`, there really is never going to be a duplicate. So, there is no need to order by anything else. So, ORDER BY `bible_concordance_words`.`word` ASC is all you should need. -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
