MySQL - ranking by count() and GROUP BY

Question

I've got my mysql table posts, where all posts of my forum are stored. It's like this:

id      uid      thread      post      title      text      time
(int)   (int)    (varchar)   (int)     (varchar)  (text)    (int)

Now I want to show the rank (ranking of number of posts) on the user profiles. I've tried something like this:

set @rownum := 0;
SELECT @rownum := @rownum + 1 AS rank, uid, count(id)
FROM `posts` GROUP BY uid ORDER BY count(id)

But it returns not the right data. The uid and count(id) match, but the rank is wrong. My entry is like:

rank     uid     count(id)
  1        1        214

I'm user 1, and I've got 214 posts, but that's not rank 1. There are other entries like:

rank     uid     count(id)
  8       22        674

How can I get the query to put out the right rank?

What do you want to happen if two or more users have the same number of posts? — pilcrow, Apr 23 '12 at 19:10
@pilcrow hm, i think this doesn't matter, it should just order ist per uid secondary — ninov, Apr 23 '12 at 19:11

DRapp · Accepted Answer · 2012-04-23T19:53:39.040

6

You need your entire result set grouped by user ID first and ordered... then apply the ranking

select
      @rownum := @rownum +1 as rank,
      prequery.uid,
      prequery.PostCount
   from
      ( select @rownum := 0 ) sqlvars,
      ( SELECT uid, count(id) postCount
           from posts
           group by uid
           order by count(id) desc ) prequery

To get for a specific person, and problem attempting the "HAVING" clause, I would then wrap it up and then apply a where...

select WrappedQuery.* 
   from ( entire query from above ) WrappedQuery
   where WrappedQuery.uid = SinglePerson

edited Apr 23 '12 at 19:53

answered Apr 23 '12 at 19:03

DRapp

47,638
12
72
142

Hmm, is it possible to return only one row for a specified uid? – ninov Apr 23 '12 at 19:10
@Ninov, yes, just add to the outermost part of the query... HAVING prequery.uid = IDOfPersonWanted – DRapp Apr 23 '12 at 19:19
@DRapp, are you sure about that HAVING guidance? I get `ERROR 1463 (42000): Non-grouping field ... is used in HAVING clause` if I add that to a substantively similar MySQL ROW_NUMBER hack. – pilcrow Apr 23 '12 at 19:29
Hm this returns rank=1 for uid 1 and (Null) for all other uids... **edit:** it returns rank=1 for each uid, which has any posts :( – ninov Apr 23 '12 at 19:30
1

@ninov, revised to wrap answer up one more level. – DRapp Apr 23 '12 at 19:54

MySQL - ranking by count() and GROUP BY

1 Answers1

Linked