Prev: auto_increment
Next: formatting a word doc using php ?
From: "Ron Piggott" on 11 Sep 2010 02:47

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

From: Karl DeSaulniers on 11 Sep 2010 03:00
Hello,
This may help.
http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

Best,
Karl


On Sep 11, 2010, at 1:47 AM, Ron Piggott 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
>

Karl DeSaulniers
Design Drumm
http://designdrumm.com

From: Richard Quadling on 13 Sep 2010 06:40
On 11 September 2010 07:47, Ron Piggott <ron.piggott(a)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`.`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
 | 
Pages: 1
Prev: auto_increment
Next: formatting a word doc using php ?