order keywords by frequency in PHP mySql

Question

Hello,

I've got a database with video ids and N keywords for each video. I made a table with 1 video ID and 1 keyword ID in each row.

What's the easiest way to order keywords by frequency? I mean to extract the number of times a keyword is used and order them.

Is it possible to do that with sql or do I need to use php arrays?

Thanks

Answer 1

If I understand you correctly you can try

SELECT  VideoID,
        KeyWordID,
        COUNT(KeyWordID) Total
FROM    VideoKeywords
GROUP BY VideoID,
        KeyWordID
ORDER BY VideoID,Total DESC

Answer 2

I don't see the need for a join here. Simply list all the keywords along with the number of times the keyword appears, ordered from most frequent to less frequent.

SELECT keyword, COUNT(*) freq 
FROM keywordTable 
GROUP BY keyword 
ORDER BY freq DESC