I have a table called 'rank' which contains information about music - more definitively, it stores peoples votes (1 to 5) on a particular song.
The table layout is as follows:
id | songid | userid | blurb | useful | useless | thetime
I have written a piece of sql that can calculate an average for each distinct songid and order them. Here it is as follows.
SELECT DISTINCT ( rank.songid ) AS song_id, ( SELECT AVG( rank.rank ) FROM rank WHERE rank.songid = song_id ) AS average_rank FROM rank ORDER BY average_rank DESC
Ultimately, I want to have a piece of script that would determine based on the average score of a song what rank it is in, out of all the other songs on the database (eg 2nd place out of 2303 songs). Is there a way to do this in SQL other than to use scripting like PHP to assign an incrementing value to each and every row returned?
I'm racking my brains - I would just like it to tell me that Song-42 is coming 203rd.