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

Question

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.

Answer 1

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()