Member Avatar for venet

Hey guys,

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.

  • 2 Contributors
  • 4 Replies
  • 107 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by rch1231
Member Avatar for rch1231

OK quick question... which field holds the rank the user gave the song?

Member Avatar for venet

SORRY! I quoted the layout of the wrong table!!

id | songid | rank | userid is correct, where 'rank' holds the rating

Sorry about that!

Member Avatar for venet

Still stumped, however I found out about variables so this has helped a little.

I broke it down to basics.

Select rank.songid, (
SELECT AVG( rank.rank )
FROM rank
WHERE rank.songid = song_id
) AS average_rank
FROM rank
ORDER BY average_rank DESC

Returns

song_id | average_rank
1 | 4.25
1 | 4.25
1 | 4.25
1 | 4.25
8 | 1.66666666666667
8 | 1.66666666666667
8 | 1.66666666666667
2 | 1

As you can see this is good information, however it is displaying the 'average_rank' for every entry so I must make the song_id distinct in order for it to be useful.

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

Returns

song_id | average_rank
1 | 4.25
8 | 1.66666666666667
2 | 1

Great! Closer now!

My next step was to add an incrementing variable for each row. I had a go at it..

select @i:=0;
Select distinct(rank.songid) AS song_id, (
SELECT AVG( rank.rank )
FROM rank
WHERE rank.songid = song_id
) AS average_rank,  @i:=@i+1 as position
FROM rank
ORDER BY average_rank DESC

Returned:
song_id | average_rank | position
1 | 4.25 | 1
1 | 4.25 | 2
1 | 4.25 | 3
1 | 4.25 | 4
8 | 1.66666666666667 | 6
8 | 1.66666666666667 | 7
8 | 1.66666666666667 | 8
2 | 1 | 5

As you can see, when this particular code parses, the distinct function seems to be rendered ineffective!

What's happening here?

Edited by venet because: tabulations in tables were not displaying correctly
Member Avatar for rch1231

What you need is a select inside a select to do this:

select @i:=0;
select @i:=@i+1 as position, data1.song_id, data1.averagerank 
from 
(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 )as data1


DESC

Try this I think it is pretty close..

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.