Than, you'd have to adjust it. This is what I get, from my test db:
+------+-----+---------------+ | row | uid | comment_count | +------+-----+---------------+ | 1 | 1 | 27 | | 2 | 4 | 13 | | 3 | 9 | 7 | | 4 | 20 | 5 | | 5 | 70 | 4 | | 6 | 110 | 3 | | 7 | 6 | 2 | | 8 | 64 | 2 | | 9 | 151 | 2 | | 10 | 152 | 2 | | 11 | 62 | 1 | | 12 | 67 | 1 | | 13 | 10 | 1 | +------+-----+---------------+ 13 rows in set (0.00 sec) But, I am not sure how this will work with LIMIT from pager. Seems like you'd have to put more logic into this, and there's a good chance that one query won't be up to the job. On Fri, 2010-10-29 at 12:47 -0700, nan wich wrote: > > 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 <[email protected]> > To: [email protected] > 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 <[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. > > > > > > > >
