Alex Rivera | Logout

SQL Group By with an Order By

Asked 2008-08-26T13:09:15.883
167

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

mysql sql mysql-error-1111
Edit
Report

1 Answer

56

MySQL prior to version 5 did not allow aggregate functions in ORDER BY clauses.

You can get around this limit with the deprecated syntax:

SELECT COUNT(id), `Tag` from `images-tags`
GROUP BY `Tag`
ORDER BY 1 DESC
LIMIT 20

1, since it's the first column you want to group on.

answered 2008-08-26T13:14:37.073

Your Answer