Re: SELECT / ORDER BY

On 11 September 2010 07:47, Ron Piggott <ron.piggott@actsministries.org> 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��T_���p > ��uP�.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