SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Guru mmarif4u's Avatar
    Join Date
    Dec 2006
    Location
    /dev/swat
    Posts
    619
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Ranking based on count of submissions

    Hi SQL gurus,

    Need little help with ranking query. I have a table which have user submissions saved. Table with data:
    Code:
    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:
    Code:
    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by mmarif4u View Post
    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.
    i can do it in sql, but it will slow down the query substantially, so it should be done in php

    your call
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru mmarif4u's Avatar
    Join Date
    Dec 2006
    Location
    /dev/swat
    Posts
    619
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,147
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    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
    Code:
    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. #5
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,147
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    And of course, I finally figured out a MySQL only solution
    Code:
    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
    Code:
        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. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,147
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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
    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.

  8. #8
    SitePoint Guru mmarif4u's Avatar
    Join Date
    Dec 2006
    Location
    /dev/swat
    Posts
    619
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #9
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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...

    Code MySQL:
    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;


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •