CODEWITHSUNDEEP
phpmysqlsqldatabasemariadb
en Arr
en Arr

Reputation: 23

storing a rank in mysql database

I am currently trying to store a position rank in a database.

I need to store where a song is ranked by their likes. At the moment I am able to store it like this:

SET @r=0;
UPDATE song_board_t SET song_ranking= @r:= (@r+1) ORDER BY song_likes DESC;

This will be something I run on a temporary table each day and then copy into another table.

THe issue I am running into is that I need the song_ranking to be equal if the song_likes are equal for example if two songs have 100 likes each they should both have the same ranking.

At the moment it just counts through the rows so each rank is one more than the previous.

I am ok if some of this needs to maybe be done in PHP rather than sql.

Any help would be appreciated.

THanks in advance

EN

Upvotes: 2

Views: 879

Answers (1)

fancyPants
fancyPants

Reputation: 51868

You can do it with this query:

UPDATE song_board_t s
INNER JOIN (
    SELECT
    song, /*or whatever the primary key is*/
    @r := IF(@prev = song_likes, @r, @r + 1) as ranking,
    @prev := song_likes
    FROM
    song_board_t
    , (SELECT @r := 0, @prev := null) var_init_subquery
    ORDER BY song_likes DESC
) sq ON s.song = sq.song
SET s.song_ranking = sq.ranking;

Upvotes: 1

Related Questions