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
This question is related to
mysql
sql
mysql-error-1111
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.
Try this query
SELECT data_collector_id , count (data_collector_id ) as frequency
from rent_flats
where is_contact_person_landlord = 'True'
GROUP BY data_collector_id
ORDER BY count(data_collector_id) DESC
You can get around this limit with the deprecated syntax: ORDER BY 1 DESC
This syntax is not deprecated at all, it's E121-03 from SQL99.
I don't know about MySQL, but in MS SQL, you can use the column index in the order by
clause. I've done this before when doing counts with group by
s as it tends to be easier to work with.
So
SELECT COUNT(id), `Tag` from `images-tags`
GROUP BY `Tag`
ORDER BY COUNT(id) DESC
LIMIT 20
Becomes
SELECT COUNT(id), `Tag` from `images-tags`
GROUP BY `Tag`
ORDER 1 DESC
LIMIT 20
In Oracle, something like this works nicely to separate your counting and ordering a little better. I'm not sure if it will work in MySql 4.
select 'Tag', counts.cnt
from
(
select count(*) as cnt, 'Tag'
from 'images-tags'
group by 'tag'
) counts
order by counts.cnt desc
Source: Stackoverflow.com