Sorting SQL results from two tables

I have two tables, tags and coupon_tags. tags contains tagName and tagID. Each unique name has a unique ID. Perhaps the tag foo may have the tagID 1, for example.

Then coupon_tags has a bunch of couponID's and a bunch of tagID's. There is one row per coupon per tag. A coupon may, of course, have more than one tag. That table may look like this:

tagID | couponID
  5        开发者_Go百科3
  4        3
  9        3
  5        6

What I'm trying to do is to get the top 10 most-used tags. I've no experience in sorting algos or heavy SQL so I'm not sure at all how to begin. Any help?

---

Do it like this:

 SELECT tagID, count(*) as TotalOccurrences from coupon_tags
    group by tagID
    order by TotalOccurrences DESC
    limit 10

This will give you most used tags IDs.

---

Use LIMIT to return only 10 rows, and use ORDER BY .. DESC to get the largest values, and use GROUP BY to count tags together.

select t.tagID, t.tagName, count(*) as TimesUsed
from coupon_tags c
inner join tags t on t.tagID = c.tagID
group by t.tagID, t.tagName
order by TimesUsed DESC
limit 10;

Note that if there are ties for the 10th place or beyond, e.g. 3 x 9th or 2 x 10th, LIMIT 10 will cause only one of them (randomly) to show. If you need to show ties that extend to the 11th spot or beyond, it will require a complicated query. A better option to handle that would be to get the top 15, then in the programming front-end, go further than 10 until the TimesUsed value changes.

---

How about this?

SELECT * FROM (
  SELECT T.tagID, 
         count(*) AS numerOfTags
    FROM tags AS T
    JOIN coupon_tags AS C
      ON C.tagID = T.tagID
   GROUP BY T.tagID
  )
 ORDER BY numerOfTags DESC
 LIMIT 10