[SOLVED] Order Results By Occurrence

Order Results By Occurrence

I have the following two tables.

BookmarkTag ( BookmarkID, TagID ) Tag ( TagID, Title)

Currently I am selecting all the tags with the appropriate BookmarkID. The problem is I want to only select the tag once to avoid duplication in the result and also only bring back the tags that occur the most.

This is my current SQL query:

SELECT Tag.Title 
FROM `Tag` INNER JOIN BookmarkTag 
WHERE BookmarkTag.BookmarkID = 1 AND Tag.TagID = BookmarkTag.TagID'

Solution

You need to put the join condition in an ON clause after the JOIN keyword. Not in the where clause.
You were mixing SQL89 with SQL92 syntax. This may work I haven't tested, but this is faster.

SELECT Tag.Title 
FROM `Tag` t
INNER JOIN BookmarkTag b ON (t.tagid = b.tagid)
WHERE B.BookmarkID = 1
GROUP BY t.tagid
ORDER BY count(*) DESC

In order to make the results unique per tag, do a group by on tagid.
Then you can order by occurrence by using count(*) to see make the tags with the highest occurrence float to the top.
(try to always use count(*) because it is faster than count(afield) )