order by a number of votes in a certain time period

**franco714** · Feb 12, 2009 20:16

right so what im trying to do is order a list of images by how many votes they got in a certain amount of time (most votes today, most votes this week, this month, etc), and i have two tables, one with the images information and the other with the votes... their structured something like this

image_table
-id
-image_title
-date_added
-etc

votes_table
-vote_id
-image_id
-date_voted
-user_id
-etc

and im completely clueless on how to do this... can anyone help?

thanks

**r937** · Feb 12, 2009 21:22

Code:

SELECT i.id
     , i.image_title
     , i.date_added
     , v.votes
  FROM image_table AS i
INNER
  JOIN ( SELECT image_id
              , COUNT(*) AS votes
           FROM votes_table
          WHERE date_voted >= '2009-01-01
            AND date_votes  < '2009-02-01'
         GROUP
             BY image_id
       ) AS v
    ON v.image_id = i.image_id

**franco714** · Feb 12, 2009 21:38

thanks, just had a couple of questions though, does it matter that the field date_voted is a timestamp, or does it have to be formated like you have it there "2009-01-01", also does that grab the votes from a certain day, or past 24 hours.

oh and what if an image doesnt have any votes that day, will it still show up...

sorry these are a lot of questions, this just seems a lot more complicated than i first imagined..

**IBazz** · Feb 12, 2009 21:54

try out rudy's suggestion to see it work.

As for you questions:

the data type for your column is determined by the content. If you only have an ISO date then the 'date' type should be adequate/best

That script will grab votes from whatever dates you have in the query ~ again, try it out.

bazz