SQL query help

[XeroSiS]

We have a bunch of videos in a table:
video_id
title
...
views_count
posted_on

Generally 10-20 videos are posted every day.

Now I need to write 1 query that gives me the most viewed video_id per day.

So if the month is 30 days, I get 30 video_id's each representing the most viewed video of a day in that month.

Is that possible or must I write 30 queries?

Thanks.

[fideltfg]

your going to need another table to store a record of all views for all videos before for can collect the data your after. with the table you described your only keeping a total count of views.

[chris0]

you need a new table with fields:

video_id
day
viewcount

and make sql updates the current date when the video is being view instead of updating the master table

you will need to make the query to

make unique key (video_id,day)

and make the update count query

"insert into views_table ....... on duplicate keys update .... "
(fill in the dots)