0

I've been looking around but couldn't find my answer anywhere.

I have a MySQL table which looks something like this:

CREATE TABLE IF NOT EXISTS `g_video_watch` (
`vwatch_id` int(11) NOT NULL,
  `vwatch_user` int(11) NOT NULL,
  `vwatch_track` int(11) NOT NULL,
  `vwatch_ip` varchar(255) NOT NULL,
  `vwatch_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=36 ;

A page where the user can watch a video inserts everytime a page opens a row with the information from who the user is (vwatch_user) what track/video he or she opens (vwatch_track) and what the IP is. Also the current timestamp is inserted for the goal I'm trying to achieve.

And that goal is to show the tracks/videos which are most watched today. So in other words, select TODAY, GROUP all tracks and check how many rows per group is returned to order in COUNT where views is the highest...

Anybody an idea?

Bart Scheffer
  • 497
  • 1
  • 3
  • 18
  • Please provide some sample data and the desired output – peterm Sep 25 '14 at 21:17
  • Here is the question that you could not find - http://stackoverflow.com/questions/17198468/from-the-timestamp-in-sql-selecting-records-from-today-yesterday-this-week-t – Bulat Sep 25 '14 at 21:21

2 Answers2

3

To get 100 most viewed tracks try this:

SELECT vwatch_track, COUNT(*) 
FROM g_video_watch
WHERE DATE(vwatch_date) = DATE(NOW())
GROUP BY vwatch_track
ORDER BY COUNT(*) DESC
LIMIT 100
Bulat
  • 6,869
  • 1
  • 29
  • 52
2
SELECT vwatch_track , count(*)
FROM    g_video_watch
WHERE  vwatch_date >= CURRENT_DATE
GROUP BY vwatch_track
radar
  • 13,270
  • 2
  • 25
  • 33