SQL Group By with an Order By
I have a table of tags and want to get the highest count tags from the list.
Sample data looks like this
id (1) tag ('night')
id (2) tag ('awesome')
id (3) tag ('night')
using
SELECT COUNT(*), `Tag` from `images-tags`
GROUP BY `Tag`
gets me back the data I'm looking for perfectly. However, I would like to organize it, so that the highest tag counts are first, and limit it to only send me the first 20 or so.
I tried this...
SELECT COUNT(id), `Tag` from `images-tags`
GROUP BY `Tag`
ORDER BY COUNT(id) DESC
LIMIT 20
and I keep getting an "Invalid use of group function - ErrNr 1111"
What am I doing wrong?
I'm using MySQL 4.1.25-Debian
Answer :
In all versions of MySQL, simply alias the aggregate in the SELECT list, and order by the alias:
SELECT COUNT(id) AS theCount, `Tag` from `images-tags`
GROUP BY `Tag`
ORDER BY theCount DESC
LIMIT 20
