[development] Ranking in a query

[nan wich]

Thanks, but it didn't do anything on my system. No errors, no "empty result", 
nothing at all.
 
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 9:32:56 AM
Subject: Re: [development] Ranking in a query

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/a14f772e/attachment.html