[BNM] mysql grouping and ordering and limiting

[Ali]

bingo, thanks for that David - works a treat and looks pretty obvious too -
I tried a similar query not sure what went wrong - probably a typo,  made me
assume combining group by and order by was a bit trickier.....

Ali

-----Original Message-----
From: bnmlist-bounces at brightonnewmedia.org
[mailto:bnmlist-bounces at brightonnewmedia.org] On Behalf Of David Pashley
Sent: 22 January 2009 17:17
To: Brighton New Media
Subject: Re: [BNM] mysql grouping and ordering and limiting

On Jan 22, 2009 at 17:00, Ali praised the llamas by saying:
> sorry quick typo in my original query should be
> 
> $q="select authorId, count(*) FROM blog WHERE 1 GROUP BY authorId";
> 
assuming a sensible SQL support:

SELECT authorId, count(authorId) as count FROM blog GROUP BY authorId ORDER
BY count DESC LIMIT 10;

Assuming you want the top 10 authors.

If you want a set of 10 authors:

SELECT authorId, count(authorId) as count FROM blog WHERE authorId in
(1,2,3,4,5,6,7,8,9,10) GROUP BY authorId ORDER BY count DESC;

Replace the numbers in the brackets with the authors you're interested
in.

> Ali
> 
> 
> 
> Hi
> 
>  
> 
> Hopefully this is a quickie for someone, my first efforts are duds and
> googling is a pain...
> 
>  
> 
> $q="select authorId, count(*) FROM blog WHERE 1 GROUP authorId";
> 
>  
> 
> Now I want to limit to, say 10 authors and order by count(*) descending -
> can anyone indulge me with the syntax?
> 
>  
> 
> Ali
> 

-- 
David Pashley
david at davidpashley.com
Nihil curo de ista tua stulta superstitione.
-- 

BNM Subscribe/Unsubscribe:
http://www.brightonnewmedia.org/options/bnmlist

BNM powered by Wessex Networks:
http://www.wessexnetworks.com
No virus found in this incoming message.
Checked by AVG - http://www.avg.com 
Version: 8.0.176 / Virus Database: 270.10.12/1910 - Release Date: 22/01/2009
18:28