Add iterative column to result set-CodePudding

I am using mysql Ver 14.14 Distrib 5.7.21, for Linux (x86_64).

I am having a table referral:

CREATE TABLE `referrals` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `referred_by` bigint(20) unsigned DEFAULT NULL,
  `referral_token` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `referrals_email_unique` (`email`)
);

In the table you basically insert the person and who referred it.

I have created the following leaderboard:

select
        referred_by,
        count(referred_by) as referred_by_count,
            @curRank := @curRank   1 AS rank
from
        referrals r,
    (
    SELECT
        @curRank := 0) o
group by
        referred_by
order by
        referred_by_count DESC;

This gives me:

| referred_by | referred_by_count | rank |
| ----------- | ----------------- | ---- |
| 10          | 3                 | 3    |
| 2           | 2                 | 1    |
| 3           | 2                 | 2    |
| 13          | 2                 | 5    |
| 11          | 2                 | 6    |
| 15          | 1                 | 8    |
| 12          | 1                 | 9    |
| 4           | 1                 | 4    |
| 9           | 1                 | 7    |

However, I would like to get:

| referred_by | referred_by_count | rank |
| ----------- | ----------------- | ---- |
| 10          | 3                 | 1    |
| 2           | 2                 | 2    |
| 3           | 2                 | 3    |
| 13          | 2                 | 4    |
| 11          | 2                 | 5    |
| 15          | 1                 | 6    |
| 12          | 1                 | 7    |
| 4           | 1                 | 8    |
| 9           | 1                 | 9    |

---

[View on DB Fiddle](https://www.db-fiddle.com/f/o42zo6XiJZzEbrrVDfgQR4/3)

I created the following dbfiddle using the faker php plugin.

I tried using the mysql RANK() function. However, I am struggeling how to correctly use it.

I appreciate your replies!

CodePudding user response：

SELECT referred_by, referred_by_count, @rank := @rank   1 `rank`
FROM ( SELECT referred_by, COUNT(*) referred_by_count 
       FROM referrals
       GROUP BY referred_by ) counts
CROSS JOIN ( SELECT @rank := 0 ) init_var
ORDER BY referred_by_count DESC, referred_by ASC;

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=6dd102f3bc88cd939efe769b5804e757

PS. referred_by is added to ORDER BY expression for to make this sorting definite and, hence, to make the output deterministic.

PPS. The query which uses UDV processing in the output list must (1) have only one source table (2) have proper ORDER BY (3) do not have GROUP BY and HAVING. If something of above needed it must be performed in subquery.