CODEWITHSUNDEEP
phpsqlmysql
Richard Knop
Richard Knop

Reputation: 83695

Calculating a row position in the table

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?

Upvotes: 0

Views: 268

Answers (4)

Steve Kass
Steve Kass

Reputation: 7184

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

Upvotes: 1

Daniel Brückner
Daniel Brückner

Reputation: 59655

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.

Upvotes: 1

Jeff Ferland
Jeff Ferland

Reputation: 18292

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

Upvotes: 2

Quassnoi
Quassnoi

Reputation: 425411

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

Upvotes: 2

Related Questions