0

I have below query in MySQL:

SELECT COUNT(c2.cus_referrer_id) as total_referrals,
                @n := @n + 1, @r := IF(@c = COUNT(c2.cus_referrer_id), @r, @n) rank, @c := COUNT(c2.cus_referrer_id)
                FROM customers c1
                INNER JOIN customers c2 ON c1.id = c2.cus_referrer_id,
                (
                    SELECT @r := 0, @n := 0, @c := NULL
                ) i
                WHERE c2.cus_referrer_id!='0'
                AND (DATE(c2.cus_date_added) BETWEEN '2014-09-15' AND '2016-10-30')
                AND c1.is_deleted='0' AND c2.is_deleted='0'
                GROUP BY c2.cus_referrer_id
                ORDER BY total_referrals DESC LIMIT 50

What I want to do is display ranking based on total referrals. If there are same referrals then keep the ranking same and next referral will get skipped ranking..

For Example:

Array
(
[0] => Array
    (
        [total_referrals] => 3
        [@n := @n + 1] => 3
        [rank] => 2
        [@c := COUNT(c2.cus_referrer_id)] => 3
    )

[1] => Array
    (
        [total_referrals] => 1
        [@n := @n + 1] => 1
        [rank] => 0
        [@c := COUNT(c2.cus_referrer_id)] => 1
    )

[2] => Array
    (
        [total_referrals] => 1
        [@n := @n + 1] => 2
        [rank] => 1
        [@c := COUNT(c2.cus_referrer_id)] => 1
    )

[3] => Array
    (
        [total_referrals] => 1
        [@n := @n + 1] => 4
        [rank] => 3
        [@c := COUNT(c2.cus_referrer_id)] => 1
    )

)

In above case, in last 3 records there are same total referrals: 1

So, forth one will get ranked as 5th.

I need output of ranking like: 1,2,2,2.

What am I doing wrong here?

RNK
  • 5,582
  • 11
  • 65
  • 133
  • 1
    See http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry Sep 09 '16 at 18:22
  • Possible duplicate of [How to rank a record with same weight in MySQL](http://stackoverflow.com/questions/39214022/how-to-rank-a-record-with-same-weight-in-mysql) – Patrick Q Sep 09 '16 at 18:33

0 Answers0