3

I have a table with multiple user records, ordered by date. Users' stats are kept there, and a new entry is made for every event.

I have created a query that fetches the top 10 users, based on occurrence:

SELECT * FROM (
    SELECT name, COUNT( * ) AS occurrences 
    FROM users 
    GROUP BY name 
    ORDER BY occurrences DESC LIMIT 10
) AS rank;

It properly reports all the top 10 users, and the number of times each shows up.

I would, however, like to also be able to search for a certain user and it'd report not only the number of times he appears, but also his overall rank. The rank would be his position in the list, if all users were to be ordered by number of occurrences.

I have been trying to use SELECT ROW_NUMBER() but I'm always getting invalid syntax, not exactly sure what am I doing wrong, unfortunately. I've been trying random things I've been finding online but none seem to work.

What would be the best way to find a user's rank? With only one query, if possible.

Thanks, Nuno

Isaac Bennetch
  • 11,830
  • 2
  • 32
  • 43
Nuno
  • 65
  • 5

1 Answers1

3

Based on the answer that i linked in the comment, i was fiddling with it and came up with this:

SELECT *
FROM (
  SELECT NAME, occurrences, @rownum := @rownum + 1 AS position
  FROM (
    SELECT NAME, COUNT(*) AS occurrences
    FROM users
    GROUP BY NAME
    ORDER BY occurrences DESC LIMIT 10
    ) a
  JOIN (
    SELECT @rownum := 0
    ) r
  ) b
WHERE NAME = "bbb"

This query returns the user and it's position based on the COUNT.

If you just want the ordered list with the ranks, just remove the outer select:

SELECT name,occurrences,@rownum := @rownum + 1 AS position
FROM (
    SELECT name, COUNT(*) AS occurrences 
    FROM users 
    GROUP BY name 
    ORDER BY occurrences DESC LIMIT 10
) a
JOIN (SELECT @rownum := 0) r;

sqlfiddle demo

Filipe Silva
  • 21,189
  • 5
  • 53
  • 68
  • Thanks a lot, it worked. By the way, on the first one you had ": =" instead of ":=", which causes syntax errors. – Nuno Oct 28 '13 at 08:35