[Resolved] Tricky (for me) mysql query

Dolemite50

Hi,

I have songs that are stored in a mysql database with a date and an overall rating. What I need to do is pull the highest rated song for each day. One song per day so that the end result would be something like this

Name Of Song 1/1/04
name of another Song 1/2/04

etc..

Here is about as far as I've gotten. 🙁

$vquery = "SELECT songid, srating, DATE_FORMAT(sdate, '%c/%e/%Y') AS sdate, stitle  FROM songs ORDER BY sdate, srating DESC";

But thats not quite right. Do I need to do a loop within a loop or something?

Thanks for any help.

planetsim

You should be ordering by srating only unless you have ratings that are equal in that case you should have it like so

$vquery = "SELECT songid, srating, DATE_FORMAT(sdate, '%c/%e/%Y') AS sdate, stitle  FROM songs ORDER srating, sdate DESC";

Dolemite50

Thanks for the help!

Hmm, that didnt work. Here is a sample of the table

Entry one:
stitle = Song Title
sdate = 12/23/2004
srating = 3.55
songid = 12

Entry two:
stitle = Song Title Two
sdate = 12/23/2004
srating = 3.59
songid = 15

Entry three:
stitle = Song Title Three
sdate = 12/22/2004
srating = 3.50
songid = 16

There are multiple entries for each day and I am trying to go back through the table and find the one highest rated song per day based on day of submission. It would look like this:

while..{
The highest rated song on $sdate was $stitle
...}

Would LIMIT ONE be used somewhere?

Dolemite50

In other words, just display the highest rated song of the day per day.

Dolemite50

I still cant get this one figured out, any other ideas?

Dolemite50

I'm thinking that a ghetto solution might be to perform a loop that starts on the first day and increments the date by one day in a loop. Can anybody tell me how I could do that? SOmething like:

$first_day = 11-26-2004
while....{

$vquery = "SELECT songid, srating, stitle  FROM songs WHERE sdate= $first_day ORDER srating, sdate DESC";

$first_day = $first_day + 1 day?

thanks

Dolemite50

I'm thinking that a ghetto solution might be to perform a loop that starts on the first day and increments the date by one day in a loop. Can anybody tell me how I could do that? SOmething like:

$first_day = 11-26-2004
while....{

$vquery = "SELECT songid, srating, stitle  FROM songs WHERE sdate= $first_day ORDER srating, sdate DESC";

$first_day = $first_day + 1 day?

thanks

Dolemite50

I'm thinking that a ghetto solution might be to perform a loop that starts on the first day and increments the date by one day in a loop. Can anybody tell me how I could do that? SOmething like:

$first_day = 11-26-2004
while....{

$vquery = "SELECT songid, srating, stitle  FROM songs WHERE sdate= $first_day ORDER srating, sdate DESC";

$first_day = $first_day + 1 day?

thanks

xblue

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

(I checked it on some rougly similar data and it seemed to work the way I understood you wanted it, so give it try)

Dolemite50

xblue,

Thanks alot! That appears to be just what I wanted. Only one slight problem, I tried to order them by sdate like this:

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

And it is not sorting correctly (12/9/04 will show up before 12/29/04)

Is there a resolution for that?

Thanks again, your solution is perfect.

Dolemite50

This seems to be working

$get = "SELECT songid, MAX(srating) as srating, sdate, DATE_FORMAT(sdate, '%c/%e/%Y') AS sdate2, stitle  FROM songs GROUP BY sdate2 ORDER BY sdate DESC";

Thanks again, I really appreciate it.