Get Rank by votes and number of same rank PHP MySQL-CodePudding

i am trying to get rank and number of same rank by votes but unfortunately no success.

Here my table structure:

| ID| user_id | votes |
| --| ------- | ----- |
| 1 | D10     | 15    |
| 2 | D5      | 9     | 
| 3 | D20     | 9     |
| 4 | D23     | 7     |   
| 5 | D35     | 3     |
| 6 | D65     | 2     |

I need the rank of user according to votes, referring to above table i need the rank as:

| user_id | Rank|
| ------- | ----|
| D10     | 1   |
| D5      | 2   | 
| D20     | 2   |
| D23     | 3   |   
| D35     | 4   |
| D65     | 5   |

and also i need the number of rank, referring to above ranks i need:

Rank 1 = 1
Rank 2 = 2
Rank 3 = 1
Rank 4 = 1
rank 5 = 1

i tried to get rank :

SELECT user_id, votes, FIND_IN_SET( votes, (
SELECT GROUP_CONCAT( DISTINCT votes
ORDER BY votes DESC ) FROM table)
) AS rank
FROM votes

the above query i tried referring to this answer to get the ranks but i am getting error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '( votes , (SELECT GROUP_CONCAT( DISTINCT votes ORDER BY votes DESC )

i need the desired result using PHP and MySQL.

CodePudding user response：

On MySQL 8 you could use windows function dense_rank and count over

with votes_rank as (
select *,
      dense_rank() over (order by votes desc) as rnk
from votes
) , count_rank as 
  ( select votes_rank.*,
           count(*)over (partition by rnk) as cnt
   from votes_rank
  ) select id,
           user_id,
           votes,
           rnk as votes_rank,
           cnt as count_rank
from  count_rank;

https://dbfiddle.uk/o1DiPyDz

Consider the following data,

CREATE TABLE votes (
  id int,
  user_id varchar(10),
  votes int );

insert into votes values  (1,'D10',15), (2,'D5 ',9), (3,'D20',9), (4,'D23',7), (7,'D50',7), (5,'D35',3), (6,'D65',2);

Result:

id    user_id votes   votes_rank  count_rank
1       D10    15         1         1
2       D5     9          2         2
3       D20    9          2         2
4       D23    7          3         2
7       D50    7          3         2
5       D35    3          4         1
6       D65    2          5         1

Edit, On MySQL version <8

select tbl.id,tbl.user_id,tbl.votes,tbl.rnk,votes_count
from (SELECT a.id,
             a.user_id,
             a.votes,
             count(b.votes) 1 as rnk
      FROM  votes a 
      left join votes b on a.votes<b.votes 
      group by  a.id,a.user_id,a.votes
      order by a.votes desc 
   ) as tbl
inner join (select rnk,count(rnk) as votes_count 
            from ( SELECT a.id,
                         a.user_id,
                         a.votes,
                         count(b.votes) 1 as rnk
                  FROM  votes a 
                  left join votes b on a.votes<b.votes 
                  group by  a.id,a.user_id,a.votes
                 order by a.votes desc 
                 ) a2
            group by rnk
          ) as tbl1 on tbl1.rnk = tbl.rnk;

https://dbfiddle.uk/XlsBjrZO