Find ranking?

Dolemite50

Hello,

I have a site that has songs with ratings. I am trying to come up with a query that would show me what Rank the song is, such as "This song is rated #123 out of 5445 songs."

Here is a sample of how I would find the top 25.

$getInfo = "SELECT songid, stitle, srating FROM songs  ORDER BY srating DESC LIMIT 25";

But when on an individual song page I would like to find the "place" of the song.

Thanks for any help. 🙂

Shrike

$songid = 123;
$rating = "SELECT songid, stitle, srating, COUNT(songid) AS total FROM songs WHERE songid = '$songid'";

$rating['total'] would be the total number of songs, presumably $rating['srating'] is the current song rating.

Dolemite50

Hi,

Thanks for the help. What I am trying to find is actually the ranking. What is its rank compared to the other songs?

(Ranked 500th out of 1000 and a score of 3.55)

City of lost chidren is a great movie btw. 🙂

Weedpacket

Hmmm. COUNT() how many records have a higher rating and add 1? Gotta go, can't say more'n that right now.

xxdrossxx

$songid = 123;
list($srating) = mysql_fetch_row(mysql_query("select srating from songs where songid = '$songid'"));

list($countRank) = mysql_fetch_row(mysql_query("select count(*) from songs where srating > '$srating'"));
$songRank = $countRank + 1;

list($countAll) = mysql_fetch_row(mysql_query("select count(*) from songs"));

echo 'Ranked ' . $songRank . ' out of ' . $countAll . '.';

make sense?