Ooops, not quite resolved.

Dolemite50

Hi,

I accidently marked a thread as resolved but I realized that it didnt quite do what I wanted it to. I have a site with several rated song entries per day. I have the following query which works perfect for going through the DB and finding the highest rated entry per day.

SELECT songid, MAX(srating), DATE_FORMAT(sdate, '%c/%e/%Y') AS sdate, stitle  FROM songs GROUP BY sdate ORDER BY sdate DESC

The problem is, I need to find the top 3 entries per day. Can anybody please help me in altering this to display the Top 3 entries instead of the Top 1? Thanks

jannoy

add
LIMIT 3

SELECT songid, MAX(srating), DATE_FORMAT(sdate, '%c/%e/%Y') AS sdate, stitle FROM songs GROUP BY sdate ORDER BY sdate DESC LIMIT 3

Dolemite50

Hi,
I had considered that but it just returns 3 days worth of top songs instead of the top 3 per day.

Thanks, I think I need to do a loop within a loop but not sure how.

jannoy

TRY FILTERING YOUR SDATE = DATE TODAY

SELECT songid, MAX(srating), DATE_FORMAT(sdate, '%c/%e/%Y') AS sdate, stitle FROM songs GROUP BY sdate ORDER BY sdate DESC where sdate='yourdate' LIMIT 3

xblue

Hi Dolemite,

that extra requirement wasn't mentioned in your other post, or did I overlook it? I don't think that the aggregate functions would be capable of that (someone please correct me if there is a way), so you'd probably go back to selecting everything and do the filtering with php.

query:
SELECT songid, srating, DATE_FORMAT(sdate, '%c/%e/%Y') AS sdate_formatted, stitle  FROM songs ORDER BY sdate DESC, srating DESC

Then you'd need a) to keep track of the current date to see when it changes and b) internally count the records to only display the first 3 for each date.

P.S.: Glad to hear you figured out the sorting problem on your own using a different alias anyway.

Roger_Ramjet

To get the highest rated song for each day

SELECT songid, sdate, MAX(rating) FROM songs GROUP BY sdate, rating ORDER BY sdate

If you are going to use an aggregate function on a column then you must group on that column.

To limit the days use a where clause/order and limit etc.

xblue

Roger, we had already been that far in the other thread dolemite mentioned - until he revealed he did not only wanted the daily maximum, but the 3 highest per day.

as I said, I don't think this is possible with aggregate functions. do you?

Roger_Ramjet

To find the top 3 per day I need to know how the ratings are stored, are they percentages or simple integers on a scale, or what?

Since, at the end of the day, the rating is set, you can store it as a simple ranking: first(1), second(2), third(3), etc. Finding the top 3 for each day, or 4, or 5, becomes a trivial 'ranking<=$i' once you have done this. All you need is a once-a-day housekeeping script that calculates and store the previous day's rankings.

Dolemite50

Thanks alot you guys.

Here is my entire situation:

On my site I have been allowing song uploads and people rate and review them. The ratings are stored in mysql as a float column. (4.05 3.99, ..). I need to design a daily script to run as a cron job, but first, I am trying to design a script that will go back through the ratings thus far and find the Top 3 rated songs per day. (Sorry, xblue, I didnt realize that I wanted to do that in the other thread until I had marked it resolved, your solution was great).

Tell me if you think this is a good idea, I designed a table called daily_winners and I am trying to calculate the top 3 songs per day in the songs table and insert them into the daily_winners table. Heres what I have that was very close:

$get = "SELECT songid, bandid, MAX(srating) as srating, sdate, DATE_FORMAT(sdate, '%c/%e/%Y') AS sdate2, stitle  FROM songs GROUP BY sdate2 ORDER BY sdate DESC";
$chk2 = mysql_query($get, $conn); 
if(mysql_num_rows($chk2)) { 
while ($row2 = mysql_fetch_array ($chk2)) { 
$songid = addslashes($row2['songid']);
$bandid = addslashes($row2['bandid']);
$srating = addslashes($row2['srating']);
$sdate = addslashes($row2['sdate']);
$stitle = addslashes($row2['stitle']);
echo "$stitle<br>";
$get3 = "INSERT INTO daily_winners SET songid='$songid', bandid='$bandid', srating='$srating', sdate='$sdate', stitle='$stitle'";
$chk3 = mysql_query($get3, $conn) or die(mysql_error()); 
}}

Thanks to xblue's help I got that far but now I need to somehow adjust it to pull the top 3 results per day instead of just the first.

Is there a way that I can just store the first date (11-15-04) as a variable and increment it by one day in a loop?

$sdate = 11-15-04;
while{...
$get = "SELECT songid, bandid, srating, DATE_FORMAT(sdate, '%c/%e/%Y') AS sdate, stitle  FROM songs WHERE sdate='$sdate' ORDER BY srating DESC LIMIT 3";

INSERT INTO...
$sdate = $sdate + 1 day;
}

It seems like that would work but I dont know how to increment $sdate by a day.

Roger_Ramjet

In truth, this comes back to how you analysed and designed your db in the first place.

Now that you have changed the application, the basic design no longer functions adequately. Rather than trying to force fit your new application into the old design, re-design the db.

That is the solution. An additional column for the ranking for all songs, not an additional table with just the top3.

You then need your insert script to calculate the new ranknigs for today. Every time a new song is added, the rankings for all of today's songs is re-calculated. You will have to allow for ties etc so it needs some thought: but once written it is finished with. This will future-proof your design and application.

Having added the ranking field and written the ranking script, you can then apply it retroactively to the existing data.