Find the top 5 commonly used hashtags?-CodePudding

Table tags, cols->id,tag_name Table photo_tags, cols->photo_id,tag_id

I have written a code

select tag_name 
from tags 
WHERE id IN(select tag_id from photo_tags 
   group by tag_id 
   order by count(tag_id) DESC limit 5 );

I have executed in MySQL workbench it shows me error that i can't use LIMIT and IN in a single query.

Can you tell me another SQL command to find the top 5 tags ?

CodePudding user response：

Different approach, use a rank function and then Inner Join to tags table:

Select t.tag_name
From tags t Inner Join (
    Select tag_id, rank() Over (Order By taguse desc) as tagrank
    From (Select tag_id,count(tagid) as taguse From phototags Group By tag_id)
) p On t.id=p.tag_id
Where tagrank<=5

CodePudding user response：

Haven`t written on MySQL, but in MS.Access the following code will work:

SELECT tag_name
FROM tags 
LEFT JOIN photo_tags on tag_name.id= photo_tags.tag_id
GROUP BY photo_tags.tag_id
ORDER BY COUNT(tag_id) DESC
LIMIT 5

it just allows to JOIN tables without showing any of the columns from it.