CODEWITHSUNDEEP
mysqlsortingrelational-database
Christoffer
Christoffer

Reputation: 492

Row order in table depending on relative table

I have two tables. One with videos (lol_videos):

ID, title, Url, ...

and a hits table (lol_hits):

ID, videoID, timestamp

I would like to sort my videos from the video table depending on how many hits it has (stored in lol_hits) and within a specific timespan.

I've tried some different stuff but i really can't i right.. so far I just get some useless mysql errors :(

$query = "SELECT `lol_videos`.*, COUNT(lol_hits.videoID) as hit_count".
" FROM `lol_videos`".
" WHERE published > $now AND pushed > 0".
" LEFT JOIN `lol_hits`".
" ON (`lol_hits`.videoID = `lol_videos`.ID)".
" ORDER BY hit_count DESC";
$result = mysql_query($query) or die(mysql_error());

Anyone got a suggestion how to solve this problem?

Upvotes: 0

Views: 72

Answers (2)

Matt MacLean
Matt MacLean

Reputation: 19656

This should work:

SELECT v.*, v2.numHits FROM (
    SELECT videoID, COUNT(ID) 'numHits' FROM lol_hits GROUP BY videoID
) AS v2 
JOIN lol_videos v ON (v.ID = v2.videoID)
ORDER BY v2.numHits DESC;

Upvotes: 1

IsisCode
IsisCode

Reputation: 2490

Something like this? 

SELECT * FROM lol_videos
LEFT JOIN lol_hits
ON lol_videos.ID = lol_hits.videoID
ORDER BY lol_hits.hit_count DESC;

Upvotes: 0

Related Questions