Fetch recently watched videos?

Question

I am using MySQL.

I have the following table structure: tablename: Videos

videoId     title            description
1           Video title 1    Some description text1
2           Video title 2    Some description text2
3           Video title 3    Some description text3
4           Video title 4    Some description text4
5           Video title 5    Some description text5

Then I have a table: WatchedVideos

id    videoId    Userid     date
1     2          234        2009-11-12 04:46:44
2     2          212        2009-11-13 04:46:44
3     3          234        2009-11-13 05:46:44
4     4          235        2009-11-13 06:46:44
5     4          235        2009-11-13 07:46:44
6     1          234        2009-11-13 08:46:44
7     1          234        2009-11-13 09:46:44

Now I want to fetch the "Videos Being Watched Now" means the list of latest 2 (thsi I'll modify according to my condition) videos which are watched by different user recently, but When I am applying join between above two tables then it gives duplicate games which I don't want, if one user watched the same videos two times then its giving that video two time, I want unique list of videos details as below:

videoId     title            description
1           Video title 1    Some description text1
4           Video title 4    Some description text4

Please tell me what type of query should I made to fetch this data?

THANKS

Answer 1

select * from Videos v, WatchedVideos w 
where v.videoID=w.videoID 
group by videoID 
order by date desc 
limit 2;

use the "group by col1,col2,..colN" clause to put results together where the fields col1..colN are ther same.

Answer 2

Use:

  SELECT v.videoid,
         v.title,
         v.description
    FROM VIDEOS v
    JOIN (SELECT wv.videoid,
                 MAX(wv.date) 'max_date'
                 COUNT(*) 'count'
            FROM WATCHEDVIDEOS wv
        GROUP BY wv.videoid
        ORDER BY count DESC
           LIMIT 2) x ON x.videoid = t.videoid
ORDER BY x.max_date DESC