Ranking based on count of submissions

Databases
1

Hi SQL gurus,

Need little help with ranking query. I have a table which have user submissions saved. Table with data:

vid	sid	fid	vtime
1	1	23	123123
2	1	21	342141
3	1	32	123132
4	2	23	342424
5	2	44	523421
6	2	33	312313
7	2	64	231231
8	3	23	524141
9	3	21	233123
10	4	09	123123
11	4	52	123131
12	4	83	534535
13	4	49	353453
14	4	19	345355
15	4	79	767464

I can count the rows based on sid by using group by with order by if i need it, and is quite fine till here. But from here i need to rank them based on the count. For example:

sid	count	rank
1	3	3
2	4	2
3	2	4
4	6	1

I can do it in PHP, but that will require extra piece of code to be written, which i am hopefull can be done via sql query.

Any help will be greatly appreciated.
Thanks

2

i can do it in sql, but it will slow down the query substantially, so it should be done in php

your call

3

I will try to do the benchmark for both and will see which one suites the requirement better.
I am just not getting it, why MySQL till now does not have rank() function like postgre have it.

Can you post the sql query, i will play around with it.
Thanks

4

I think I understand what @r937 ; is trying to say (took me a few minutes of trying to get a row_number displayed in MySQL before I realized it, but I did realize it.

In short you can use this query

SELECT sid, COUNT(*) as count
FROM rank_posts
GROUP BY sid
ORDER BY count DESC

It will return your user’s ids with their posts counts. The data is returned showing the user with the most posts first to the person with the least post counts.
As you loop through these in PHP, each row index is their rank.

So the first record returned is Rank #1, the second #2, and so on (there is little need for MySQL to return that number for you).

5

And of course, I finally figured out a MySQL only solution

SELECT sid, count, @i := @i + 1 as rank
FROM (
    SELECT sid, COUNT(*) as count
    FROM rank_posts
    GROUP BY sid
    ORDER BY count DESC
) user_counts, (select @i := 0) row_number

I had to make

    SELECT sid, COUNT(*) as count
    FROM rank_posts
    GROUP BY sid
    ORDER BY count DESC

a derived table because otherwise the rank was not in the right order, it was writing the rank before the ORDER BY was performed. Making it a derived table, I could order the data first, then write the rank.

6

i keep forgetting those extremely useful user variables that mysql lets you use in queries (note to postgresql: pffft)

nice one, cp

it’s not as inefficient as my non-variable solution would’ve been

7

I actually had to re-learn the variables myself, as I’ve obviously been working in MSSQL too long, my first though was using ROW_NUMBER OVER (PARTITION BY) syntax, imagine my surprise when that bombed in MySQL. :rolleyes:

8

Thanks cpradio and r937 for your valuable input on this, much appreciated.

I used var for it and i was pretty sure that is the way to do it, but i did not make it work.
The query posted by cpradio works fine, i will also try the PHP method and see which one is working nicely.

9

A bit late, and only because it’s a slow day and no one else mentioned it already - because it’s probably slower than any and all of the solutions already mentioned…

SELECT x.*
     , COUNT(*) rank
  FROM
     ( SELECT sid
            , COUNT(*) cnt
         FROM tabledata
        GROUP
           BY sid
     ) x
  JOIN
     ( SELECT sid
            , COUNT(*) cnt
         FROM tabledata
        GROUP
           BY sid
     ) y
    ON y.cnt >= x.cnt
 GROUP
    BY sid;