f



how to order, then limit, then order?

Hello.  I hope this question isn't too silly.  I am working on a PHP/
mysql application that has a list of (scientific research) papers, a
list of keywords, and a list that makes links between the two.

What I'm trying to do is to show just the most popular keywords, and
to show them in alphabetical order.

My approach (illustrated at the end, with a query that workd) is to
order by a count of use, and then to limit the results.  But, as I
imagine the clever folks who read this group will see quickly, the
query leaves the keywords ordered by popularity.

Q: is there a way that I can get Mysql to take the results of this
query and then order them alphabetically, or should I be doing that in
PHP?  (I assume it would be faster to do it in mysql.  And, I may as
well admit it, I am enjoying learning mysql, so I'd like to get this
task done in that domain instead of in PHP.)

Many thanks, to anyone who is kind enough to offer some advice.

Dan.

THE QUERY IN QUESTION --


SELECT Keyword, KeywordID, count( PaperID )
FROM Papers_Keywords
LEFT JOIN Keywords ON ( Keywords.id = Papers_Keywords.KeywordID )
GROUP BY KeywordID
ORDER BY count( Keyword ) DESC
LIMIT 0 , 5;

0
7/21/2007 11:46:11 PM
comp.databases.mysql 6944 articles. 1 followers. laredotornado (869) is leader. Post Follow

2 Replies
1279 Views

Similar Articles

[PageSpeed] 10

On Sun, 22 Jul 2007 01:46:11 +0200, dankelley <Dan.Kelley@Dal.Ca> wrote:=


> Hello.  I hope this question isn't too silly.  I am working on a PHP/
> mysql application that has a list of (scientific research) papers, a
> list of keywords, and a list that makes links between the two.
>
> What I'm trying to do is to show just the most popular keywords, and
> to show them in alphabetical order.
>
> My approach (illustrated at the end, with a query that workd) is to
> order by a count of use, and then to limit the results.  But, as I
> imagine the clever folks who read this group will see quickly, the
> query leaves the keywords ordered by popularity.
>
> Q: is there a way that I can get Mysql to take the results of this
> query and then order them alphabetically, or should I be doing that in=

> PHP?  (I assume it would be faster to do it in mysql.  And, I may as
> well admit it, I am enjoying learning mysql, so I'd like to get this
> task done in that domain instead of in PHP.)
>
> Many thanks, to anyone who is kind enough to offer some advice.
>
> Dan.
>
> THE QUERY IN QUESTION --


SELECT * FROM (
SELECT Keyword, KeywordID, count( PaperID )
 FROM Papers_Keywords
LEFT JOIN Keywords ON ( Keywords.id =3D Papers_Keywords.KeywordID )
GROUP BY KeywordID
ORDER BY count( Keyword ) DESC
LIMIT 5;
) ORDDER BY Keyword

-- =

Rik Wasmus
0
Rik
7/22/2007 1:21:43 AM
Thanks very much, Rik.  It turns out that I also needed an "As"
clause.  So, for anyone who has come upon this thread because they
were having a similar problem, the full, working answer is as follows.

SELECT *
FROM (

SELECT Keyword, KeywordID, count( PaperID )
FROM Papers_Keywords
LEFT JOIN Keywords ON ( Keywords.id = Papers_Keywords.KeywordID )
GROUP BY KeywordID
ORDER BY count( Keyword ) DESC
LIMIT 0 , 5
) AS TopKeywords
ORDER BY Keyword
LIMIT 0 , 30;

0
dankelley
7/22/2007 11:27:45 AM
Reply: