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
  • 117 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by rch1231

Dani AI

Generated

Short summary of the problem and the clean solutions tried so far: was calculating per-song averages but was still getting multiple rows because the query was selecting from the votes table directly. suggested an outer select with a numbering variable. Both approaches can work, but there are clearer, safer options depending on your MySQL version.

For MySQL 8.0 or newer use window functions. First compute one row per song (GROUP BY), then apply a ranking function to assign positions. This also lets you choose how to handle ties (ROW_NUMBER, RANK, DENSE_RANK). See MySQL window functions for details: MySQL window functions.

WITH per_song AS (
  SELECT songid, AVG(`rank`) AS avg_rank
  FROM `rank`
  GROUP BY songid
)
SELECT songid, avg_rank,
       ROW_NUMBER() OVER (ORDER BY avg_rank DESC)    AS row_num,
       RANK()       OVER (ORDER BY avg_rank DESC)    AS rank_with_ties,
       DENSE_RANK() OVER (ORDER BY avg_rank DESC)    AS dense_rank
FROM per_song
ORDER BY avg_rank DESC;

For older MySQL (pre-8.0) compute averages with GROUP BY in a derived table, then use a user variable in the outer query to produce an incrementing position:

SET @pos := 0;
SELECT (@pos := @pos + 1) AS position, t.songid, t.avg_rank
FROM (
  SELECT songid, AVG(`rank`) AS avg_rank
  FROM `rank`
  GROUP BY songid
  ORDER BY avg_rank DESC
) AS t;

To return the position of a single song without listing every song, count how many songs have a strictly higher average and add one. That avoids assigning row numbers to the whole result set:

SELECT 1 + COUNT(*) AS position
FROM (
  SELECT songid, AVG(`rank`) AS avg_rank
  FROM `rank`
  GROUP BY songid
) AS t
WHERE t.avg_rank > (
  SELECT AVG(`rank`) FROM `rank` WHERE songid = 42
);

Notes and cautions: choose ROW_NUMBER vs RANK vs DENSE_RANK based on whether ties should share the same place. Index songid and consider maintaining a precomputed summary table (avg and count per song) if the votes table is large or rankings are shown often—recomputing aggregates on-the-fly can be expensive.

Recommended Answers

All 4 Replies

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.