<p>I have a table of tags and want to get the highest count tags from the list.</p>

<p>Sample data looks like this</p>

<pre><code>id (1) tag ('night')
id (2) tag ('awesome')
id (3) tag ('night')
</code></pre>

<p>using</p>

<pre><code>SELECT COUNT(*), `Tag` from `images-tags`
GROUP BY `Tag`
</code></pre>

<p>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.</p>

<p>I tried this...</p>

<pre><code>SELECT COUNT(id), `Tag` from `images-tags`
GROUP BY `Tag`
ORDER BY COUNT(id) DESC
LIMIT 20
</code></pre>

<p>and I keep getting an "Invalid use of group function - ErrNr 1111"</p>

<p>What am I doing wrong?</p>

<p>I'm using MySQL 4.1.25-Debian</p>

[{'answer_id': 27992, 'author': 'Lasse V. Karlsen', 'author_id': 267, 'author_profile': 'https://Stackoverflow.com/users/267', 'pm_score': 6, 'selected': False, 'text': "<p>MySQL prior to version 5 did not allow aggregate functions in ORDER BY clauses.</p>\n\n<p>You can get around this limit with the deprecated syntax:</p>\n\n<pre><code>SELECT COUNT(id), `Tag` from `images-tags`\nGROUP BY `Tag`\nORDER BY 1 DESC\nLIMIT 20\n</code></pre>\n\n<p>1, since it's the first column you want to group on.</p>\n"}, {'answer_id': 27994, 'author': 'jerhinesmith', 'author_id': 1108, 'author_profile': 'https://Stackoverflow.com/users/1108', 'pm_score': 4, 'selected': False, 'text': "<p>I don't know about MySQL, but in MS SQL, you can use the column index in the <code>order by</code> clause.  I've done this before when doing counts with <code>group by</code>s as it tends to be easier to work with.</p>\n\n<p>So</p>\n\n<pre><code>SELECT COUNT(id), `Tag` from `images-tags`\nGROUP BY `Tag`\nORDER BY COUNT(id) DESC\nLIMIT 20\n</code></pre>\n\n<p>Becomes</p>\n\n<pre><code>SELECT COUNT(id), `Tag` from `images-tags`\nGROUP BY `Tag`\nORDER 1 DESC\nLIMIT 20\n</code></pre>\n"}, {'answer_id': 28686, 'author': 'JosephStyons', 'author_id': 672, 'author_profile': 'https://Stackoverflow.com/users/672', 'pm_score': 3, 'selected': False, 'text': "<p>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.</p>\n\n<pre><code>select 'Tag', counts.cnt\nfrom\n  (\n  select count(*) as cnt, 'Tag'\n  from 'images-tags'\n  group by 'tag'\n  ) counts\norder by counts.cnt desc\n</code></pre>\n"}, {'answer_id': 30503, 'author': 'Scott Noyes', 'author_id': 3254, 'author_profile': 'https://Stackoverflow.com/users/3254', 'pm_score': 9, 'selected': True, 'text': '<p>In all versions of MySQL, simply alias the aggregate in the SELECT list, and order by the alias:</p>\n\n<pre><code>SELECT COUNT(id) AS theCount, `Tag` from `images-tags`\nGROUP BY `Tag`\nORDER BY theCount DESC\nLIMIT 20\n</code></pre>\n'}, {'answer_id': 31380216, 'author': 'Ashutosh Gupta', 'author_id': 4527338, 'author_profile': 'https://Stackoverflow.com/users/4527338', 'pm_score': -1, 'selected': False, 'text': "<p>Try this query</p>\n\n<pre><code> SELECT  data_collector_id , count (data_collector_id ) as frequency \n    from rent_flats \n    where is_contact_person_landlord = 'True' \n    GROUP BY data_collector_id \n    ORDER BY count(data_collector_id) DESC\n</code></pre>\n"}]
['https://Stackoverflow.com/questions/27983', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1477/']