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 <[email protected]> > To: [email protected] > 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. > >
