CODEWITHSUNDEEP
mysql
Darshit Gajjar
Darshit Gajjar

Reputation: 711

How to give rank in query according to points

The below query is working absolutely fine, as I need. All the user get unique RANKS (User of same points should not get same rank) 

SELECT  
    id, 
    first_name, 
    email, 
    (SELECT 
        rank 
    FROM (  SELECT 
            @rownum:=@rownum+1 rank, 
            u.id AS user_id, 
            points 
        FROM 
            user_master u, (SELECT @rownum:=0) r
        ORDER BY 
            points 
        DESC) AS tmp 
    WHERE 
        user_id = um.id) AS Rank,
    registered_date AS registered,
    um.points as Points
FROM 
    user_master um
ORDER BY 
    um.id ASC

Now I want to make view for this query, it gives me error message

View's SELECT contains a subquery in the FROM clause

I've also tried first to make a view of user ranks to merge 2 different views. The below query gives perfect rankings of user but when I try to make view of this:

SELECT 
    @rownum:=@rownum+1 rank, 
    id AS user_id, 
    points 
FROM 
    user_master u, (SELECT @rownum:=0) r
ORDER BY 
    points 
DESC

..it gives me error message:

View's SELECT contains a variable or parameter

Is there any other way to apply rank in this query (Rank must be unique even if points are same).

Upvotes: 3

Views: 269

Answers (1)

Tom Mac
Tom Mac

Reputation: 9853

Give this a go:

create view test_view as SELECT t.id,t.first_name,t.email,
    (select sum(case when t1.points > t.points then 1
                     when t1.points = t.points and t1.id < t.id then 1
                else 0 end) from user_master t1)+1 as rank, t.registered_date AS registered,
t.points as Points 
from user_master t
order by points desc;

Upvotes: 2

Related Questions