How to order by COUNT(*) of unique entries in GROUP BY? I search through a tag_map as
SELECT ArticleID,COUNT(*) AS C FROM WordMap 
WHERE WordID IN(
SELECT WordID FROM Words WHERE Word IN ('word1', 'word2')
) 
GROUP BY ArticleID 
ORDER BY C DESC

I want to get article ids in which all words (here word1 and word2) appear first.
The above query returns in order of the number (e.g., if word1 appears 100 times).
NOTE: WordMap table is huge, and this is the reason I went for sub-query rather than JOIN.
I answer this question because @ypercubeᵀᴹ was reluctant to do so, and I thought it might help others.
The tricky part is to use COUNT(DISTINCT WordID) instead of COUNT(*), as the latter does not consider the repetition.
As suggested by @ypercubeᵀᴹ, the ultimate solution is to use COUNT(DISTINCT WordID) and COUNT(*) together to have a perfect order considering the number of unique words and total occurrence.
The query looks like
SELECT ArticleID,COUNT(*) AS C, COUNT(DISTINCT WordID) AS D FROM WordMap 
WHERE WordID IN(
SELECT WordID FROM Words WHERE Word IN ('word1', 'word2')
) 
GROUP BY ArticleID 
ORDER BY D DESC, C DESC