www.webdeveloper.com
Results 1 to 3 of 3

Thread: SQL query help

  1. #1
    Join Date
    Aug 2005
    Posts
    220

    SQL query help

    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.

  2. #2
    Join Date
    Jan 2011
    Posts
    62
    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.

  3. #3
    Join Date
    Sep 2011
    Posts
    27
    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)

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles