Find all records that have X tags ordered by ascending time, BUT, the ones that match the most tags should be grouped first

Ok, this is a little hard to explain, and the title is not the best :/ but, basically, I have 3 tables and I’m trying to write a query that returns them with 2 levels of ordering. The first by the amount of “tags” each row has, and the second by when that row was created.

Table1: Items
  * ID
  * Name
  * CreatedAt

Table2: Tags
  * ID
  * Name

Table3: TaggedItems
  * TagID
  * ItemID

And I would like to list all items that have 1 or more tags, in ascending order by when they when created. But, and here’s where I’m having issues, I want the ordering to be based on the number of tags that match an item, and, within that ordering, apply the ascending criteria.

So, let’s say I have:

Item1 -> CreatedAt(0), Tags(A, B)
Item2 -> CreatedAt(0), Tags(A)
Item3 -> CreatedAt(1), Tags(B)
Item4 -> CreatedAt(1), Tags(A, B)

Then searching for all items with tags A and B must return first Item1 and Item4 and order those by ascending CreatedAt. And then Item2 and Item3 and order those by CreatedAt ascending. So the result would be:

* Item1
* Item4
* Item2
* Item3

I am inexperienced with SQL so maybe there are very well known solutions to this if I’m even explaining it correctly?

Appreciate any help!

Answer

Use a LEFT join of Items to TaggedItems (just in case an item does not have any tags at all) and group by item.
Finally sort by the number of tags descending and then by the date:

SELECT i.name
FROM Items i LEFT JOIN TaggedItems t 
ON t.ItemID = i.ID
GROUP BY i.ID, i.Name
ORDER BY COUNT(t.TagID) DESC, i.CreatedAt

The table Tags is not needed.

But if you want the results for a list of tags like 'A' and 'B' then:

SELECT i.name
FROM Items i 
LEFT JOIN TaggedItems ti ON ti.ItemID = i.ID
LEFT JOIN Tags t ON t.ID = ti.TagID AND t.Name IN ('A', 'B')
GROUP BY i.ID, i.Name
ORDER BY COUNT(t.ID) DESC, i.CreatedAt