SQL Group By with an Order By

Question

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

User · Answer

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

User · Answer

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 bys 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

User · 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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] SQL Group By with an Order By

Examples related to mysql

Examples related to sql

Examples related to mysql-error-1111