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?