Calculating a row position in the table

Question

Let's say I have a table like this (this is just a simplified example, the real table I'm talking about is much more complex):

CREATE TABLE media (
id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
description TEXT NOT NULL,
voted INT NOT NULL DEFAULT 0,
rating FLOAT NOT NULL DEFAULT 0
) ENGINE = INNODB;

The voted column represents a number of votes the item has received and the rating column represents the total rating of the item.

Now, what I want to do is select a single item from the table based on id, something like:

SELECT m.* FROM media AS m WHERE id = 5;

But, in addition, I want to calculate the position of this row based on the rating column and fetch that as an additional column, let's say called a site_rank (so the bigger the rating of the row is the higher its site_rank will be, I hope I explained it well). My guess is this can be achieved with a subselect query but I'm not sure how to do it.

Any help?

Answer 1

This will output high rank for most voted media.

If you want low rank for the most voted (like, the most voted gets the rank of 1), just reverse the sign in the subquery.

SELECT  mo.*,
        (
        SELECT  COUNT(*)
        FROM    media mi
        WHERE   (mi.rating, mi.id) <= (mo.rating, mo.id)
        ) AS rank
FROM    media mo
WHERE   mo.id = 5

Answer 2

SELECT
    m1.*,
    (SELECT COUNT(*) FROM media AS m2 WHERE m2.rating > m1.rating) AS site_rank
FROM
    media AS m1
WHERE
    id = 5;

Note that this does not define a complete ordering because there might be items with equal rating and they will report the same site rank.

Answer 3

SELECT count(*) FROM media WHERE rating > (SELECT rating FROM media WHERE id = 5);

Answer 4

Does MySQL support the standard SQL rank() function? It does exactly what you want.

select
  *,
  rank() over (
    order by rating desc
  ) as site_rank
from media