CODEWITHSUNDEEP
phpmysqljoin
sismaster
sismaster

Reputation: 181

find rank in mysql after multiple join

I am trying to find rank by the total points that a users earn. I do have this query but not getting idea on how to proceed.

SELECT
wp_grank.ID ,
wp_grank.competitor_ID ,
wp_grank.tournament_ID ,
wp_grank.academy_ID ,
wp_grank.division_ID,
wp_competitor.first_name,
wp_competitor.last_name,
wp_competitor.global_ID,
wp_divisions.gender,
IF(wp_divisions.age_level IN ('Tiny Kids','Kids','Pre Teen','Teen'),CONCAT('(',wp_divisions.gender,')'),'') as filterGender,
wp_tournaments.`name` as tournament_name,
wp_divisions.name as dname,
CONCAT_WS(' >> ', wp_divisions.division_type, wp_divisions.experience_level, wp_divisions.age_level, wp_divisions.weight_class, wp_divisions.gender) as division,
wp_academies.`name` as academy ,
SUM(wp_grank.points) as totalPoints
FROM
`wp_grank`
LEFT JOIN
wp_competitor
ON
wp_grank.competitor_ID = wp_competitor.ID
LEFT JOIN
wp_tournaments
ON
wp_grank.tournament_ID = wp_tournaments.ID
LEFT JOIN
wp_academies
ON
wp_grank.academy_ID = wp_academies.ID
LEFT JOIN
wp_divisions
ON
wp_grank.division_ID = wp_divisions.ID
WHERE
wp_divisions.ID IN (2) AND
YEAR(wp_tournaments.tournament_date)=2015
Group BY
wp_competitor.ID
ORDER BY
SUM(wp_grank.points) DESC ,wp_competitor.`last_name` ASC

Upvotes: 1

Views: 48

Answers (3)

Zymon Castaneda
Zymon Castaneda

Reputation: 759

You can try this one: 

SET
    @prev = 0,
    @curr = 0,
    @rank = 1,
    @i = 1;

SELECT
    @prev := @curr,
    @rank := IF(
        @prev = @curr,
        @rank,
        @rank + @i
    ) AS rank,
    IF(
        @prev != SUM(wp_grank.points),
        @i := 1,
        @i := @i + 1
    ) AS counter,
    wp_grank.ID,
    wp_grank.competitor_ID ,
    wp_grank.tournament_ID ,
    wp_grank.academy_ID ,
    wp_grank.division_ID,
    wp_competitor.first_name,
    wp_competitor.last_name,
    wp_competitor.global_ID,
    wp_divisions.gender,
    IF(
        wp_divisions.age_level IN ('Tiny Kids','Kids','Pre Teen','Teen'),
        CONCAT('(',wp_divisions.gender,')'),
        ''
    ) as filterGender,
    wp_tournaments.`name` as tournament_name,
    wp_divisions.name as dname,
    CONCAT_WS(
        ' >> ',
        wp_divisions.division_type,
        wp_divisions.experience_level,
        wp_divisions.age_level,
        wp_divisions.weight_class,
        wp_divisions.gender
    ) as division,
    wp_academies.`name` as academy,
    @curr := SUM(wp_grank.points) as `totalPoints`
FROM
    `wp_grank`
    LEFT JOIN wp_competitor ON wp_grank.competitor_ID = wp_competitor.ID
    LEFT JOIN wp_tournaments ON wp_grank.tournament_ID = wp_tournaments.ID
    LEFT JOIN wp_academies ON wp_grank.academy_ID = wp_academies.ID
    LEFT JOIN wp_divisions ON wp_grank.division_ID = wp_divisions.ID
WHERE
    wp_divisions.ID IN (2)
    AND YEAR(wp_tournaments.tournament_date)=2015
GROUP BY wp_competitor.ID
ORDER BY
    `totalPoints` DESC,
    wp_competitor.`last_name` ASC;

Note:

  • For the ranking with tied record(s) you need, I used session variable to define if the rank will increase or retain based in the previous value.

Cheers

Upvotes: 1

T Gray
T Gray

Reputation: 712

Try this:

define a variable:

SET @row_number = 0;

Then wrap the query you have in:

select @row_number:=@row_number + 1) AS rank, [all the other columns here]
from ( [your nasty query here ] ) as myNastyNastyQuery;

Just make sure your ordering is done inside the nasty query. LOL

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269743

I am guessing that you just want to add a ranking column to the query. If so:

set @rank = 0

select (@rank := @rank + 1) as rank, 
       . . .

Sometimes, variables don't work well with group by, so you need a subquery:

select (@rank := @rank + 1) as rank, q.*
from (<your query here>) q cross join
     (select @rank := 0) params;

Upvotes: 1

Related Questions