MySQL to find rank of specific user if multiple users have same values

user_6011574 0
Viewed 444

I have a table like this that keeps a total counts of a users against different types.

 CREATE TABLE IF NOT EXISTS `records` (
  `id` int(6) unsigned NOT NULL,
  `sub_id` varchar(200) NOT NULL,
  `count` int(11) unsigned NOT NULL,
  `type` int(1) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `records` (`id`, `sub_id`, `count`, `type`) VALUES
(1, 'asfand', 200, 1),
(2, 'saba', 70, 2),
(3, 'faisal', 250, 1),
(4, 'ali', 250, 1),
(5, 'khan', 100, 1),
(6, 'sidra', 150, 1),
(7, 'ayesha', 300, 1);

I want to get rank of specific sub_id against type = 1 only.

So let me rank people based on highest count score in desc order.

+----+--------+-------+------+------+
| id | sub_id | count | type | rank |
+====+========+=======+======+======+
| 7  | ayesha | 300   | 1    | 1    |
| 3  | faisal | 250   | 1    | 2    |
| 4  | ali    | 250   | 1    | 3    |
| 1  | asfand | 200   | 1    | 4    |
| 6  | sidra  | 150   | 1    | 5    |
| 5  | khan   | 100   | 1    | 6    |
+----+--------+-------+------+------+

I already have a query that returns the above second table, Which works fine. But I want to get rank of a specific user.

For example, If I want to find rank of ayesha, it is 1. Similarly If I want to find the rank of asfand, it is 4.

But When I try to find the rank of Faisal, it returns 2 and similarly for ali, it returns the same rank '2'.

Below is my query, its working fine for all records, but if 2 people have same count, then it is not working properly, it is returning rank 2 for faisal and ali both.

SELECT count(count)+1 AS rank 
FROM records WHERE count > (SELECT count FROM records WHERE sub_id= 'ali' and type = 1) 
AND type = 1 ORDER BY count DESC;

Here is my sql fiddle and please note that I have over 1 millions rows for each record.

mysql-8.0 mysql sql
2 Answers

You can use ROW_NUMBER() window function such as

SELECT q.rnk
  FROM ( SELECT ROW_NUMBER() OVER (ORDER BY `count` DESC, `id`) AS rnk,
                r.*
           FROM `records` AS r
          WHERE `type` = 1 ) AS q
 WHERE q.`sub_id` = 'ali' -- 'faisal'

Demo

most probably you're currently using DENSE_RANK() or RANK() functions(including ORDER BY count DESC) those may yield equal rank values unlike to ROW_NUMBER()

user_58413060

Your idea not to rank all rows, but only count to the row you want sounds good. But your ranking is incomplete, because you only consider the column count in your ranking, while in case of ties you also want to use the column id. (You mention this in your request comments.) The query becomes less readable though:

SELECT COUNT(r.count) AS rnk
FROM records r
WHERE r.type = 1
AND EXISTS
(
  SELECT *
  FROM records r2
  WHERE r2.type = 1
  AND r2.sub_id = 'ali'
  AND (r.count > r2.count OR (r.count = r2.count AND r.id <= r2.id))
);

I suggest two indexes for this:

Assuming that the name (sub_id) is more selective than the type (generally speaking there are few types, but many names), you can provide this index to look up the desired row quickly:

CREATE INDEX idx1 ON records (sub_id, type, count);

Then you want to look up all rows by their count column, so provide another index for this. Again I am assuming that the count column may be more selective than the type column.

CREATE INDEX idx1 ON records (count, type);

If the type column happens to be extremely selective (e.g. there are a million rows in the table, but only 100 with type 1), then move type to the first position in these indexes.

user_22707620
Related