Re: SELECT / ORDER BY

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux