[development] Ranking in a query

[Nikola Kotur]

Here it is:

select @row := @row + 1 as row, uid, comment_count from (SELECT c.uid,
COUNT(c.cid) AS comment_count FROM comments c WHERE c.uid <> 0 GROUP BY
c.uid ORDER BY comment_count DESC) as com_count, (SELECT @row := 0) r

Note: I've changed comments.mail to comments.uid, since my testing db
has mail fields empty.

But, I am warning you that this query might brake if you put it through
db_query_rewrite.

On Fri, 2010-10-29 at 05:35 -0700, nan wich wrote:
> 
> Thanks, but that doesn't do what I want. What I need is the row number
> that results from this query (simplified for example).
>  
> SELECT c.mail, COUNT(c.cid) AS comment_count FROM comments c WHERE
> c.mail <> '' GROUP BY c.mail ORDER BY comment_count DESC
>  
> And remember that pager_query is going to add LIMIT xxx, 10.
>  
> 
> Nancy
> 
>  
> 
> Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L.
> King, Jr.
> 
> 
> 
> 
> 
> 
> 
> ______________________________________________________________________
> From: Nikola Kotur <kotnick at gmail.com>
> To: development at drupal.org
> Sent: Fri, October 29, 2010 5:19:17 AM
> Subject: Re: [development] Ranking in a query
> 
> On Thu, 2010-10-28 at 06:32 -0700, nan wich wrote:
> 
> > I have a query that counts comments and all is well with it. But I'd
> > like add a ranking column to the display. The problem is that it
> > uses tablesort, so the ranking has to be in the query because I
> > don't know which column will be used to sort the display. Oh, it is
> > also paged so I can't count on the first row being #1 and so on.
> > Has anyone got an idea on how to add a ranking column to the query?
> > (MySql only.) 
> 
> 
> Nancy, here's a query that returns a nid, and it's row position:
> 
> SELECT @row := @row + 1 as row, n.nid FROM node n, (SELECT @row := 0)
> r;
> 
> You can start from there.
> 
> 


-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.drupal.org/pipermail/development/attachments/20101029/16926888/attachment.html