<html><head><style type="text/css"><!-- DIV {margin:0px;} --></style></head><body><div style="font-family:arial, helvetica, sans-serif;font-size:12pt"><DIV></DIV>
<DIV>Thanks, but it didn't do anything on my system. No errors, no "empty result", nothing at all.<BR> </DIV>
<P><FONT face="bookman old style, new york, times, serif" color=#ff007f size=4><EM><STRONG>Nancy</STRONG></EM></FONT></P>
<P> </P>
<P><FONT face="arial, helvetica, sans-serif">Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr.</FONT></P>
<DIV><BR></DIV>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: arial, helvetica, sans-serif"><BR>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: times new roman, new york, times, serif"><FONT face=Tahoma size=2>
<HR SIZE=1>
<B><SPAN style="FONT-WEIGHT: bold">From:</SPAN></B> Nikola Kotur <kotnick@gmail.com><BR><B><SPAN style="FONT-WEIGHT: bold">To:</SPAN></B> development@drupal.org<BR><B><SPAN style="FONT-WEIGHT: bold">Sent:</SPAN></B> Fri, October 29, 2010 9:32:56 AM<BR><B><SPAN style="FONT-WEIGHT: bold">Subject:</SPAN></B> Re: [development] Ranking in a query<BR></FONT><BR>Here it is:<BR><BR>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<BR><BR>Note: I've changed comments.mail to comments.uid, since my testing db has mail fields empty.<BR><BR>But, I am warning you that this query might brake if you put it through db_query_rewrite.<BR><BR>On Fri, 2010-10-29 at 05:35 -0700, nan wich wrote:
<BLOCKQUOTE TYPE="CITE"><BR></BLOCKQUOTE>
<BLOCKQUOTE TYPE="CITE">Thanks, but that doesn't do what I want. What I need is the row number that results from this query (simplified for example). </BLOCKQUOTE>
<BLOCKQUOTE TYPE="CITE"> </BLOCKQUOTE>
<BLOCKQUOTE TYPE="CITE">SELECT c.mail, COUNT(c.cid) AS comment_count FROM comments c WHERE c.mail <> '' GROUP BY c.mail ORDER BY comment_count DESC </BLOCKQUOTE>
<BLOCKQUOTE TYPE="CITE"> </BLOCKQUOTE>
<BLOCKQUOTE TYPE="CITE">And remember that pager_query is going to add LIMIT xxx, 10.<BR> </BLOCKQUOTE>
<BLOCKQUOTE TYPE="CITE"><B><I><FONT size=4><FONT color=#ff007f>Nancy</FONT></FONT></I></B><BR><BR> <BR><BR>Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr.<BR><BR></BLOCKQUOTE>
<BLOCKQUOTE TYPE="CITE"><BR><BR></BLOCKQUOTE>
<BLOCKQUOTE TYPE="CITE"><BR></BLOCKQUOTE>
<BLOCKQUOTE TYPE="CITE">
<HR SIZE=1>
<BR><B><FONT size=2>From:</FONT></B><FONT size=2> Nikola Kotur <kotnick@gmail.com></FONT><BR><B><FONT size=2>To:</FONT></B><FONT size=2> development@drupal.org</FONT><BR><B><FONT size=2>Sent:</FONT></B><FONT size=2> Fri, October 29, 2010 5:19:17 AM</FONT><BR><B><FONT size=2>Subject:</FONT></B><FONT size=2> Re: [development] Ranking in a query</FONT><BR><BR>On Thu, 2010-10-28 at 06:32 -0700, nan wich wrote:<BR>
<BLOCKQUOTE TYPE="CITE">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.) <BR></BLOCKQUOTE><BR>Nancy, here's a query that returns a nid, and it's row position:<BR><BR>SELECT @row := @row + 1 as row, n.nid FROM node n, (SELECT @row := 0) r;<BR><BR>You can start from there.<BR><BR><BR></BLOCKQUOTE><BR><BR></DIV></DIV></div></body></html>