All In One Script



PHP,HTLM,CSS,Jquery,AJAX,Javascript and etc doubts and sample codes

  • Home
  • Javascript
  • PHP
  • CSS
  • SQL/MYSQL

SQL Group By with an Order By

by Blogger 4:22:00 AM Group By MYSQL Order By SQL

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







READ MORE
SHARE :

Search This Blog

Followers

  • Popular
  • Recent
  • Comments
    How to get Real IP,ISP,Country,City and etc from Visitor using PHP
    How to efficiently iterate over each Entry in a Map?
    In Java, difference between default, public, protected, and private
    How to check if PHP array is associative or sequential?
    Length of a JavaScript object
    How to return the date part only from a SQL Server datetime datatype
    Does finally always execute in Java?
    What is the difference between “INNER JOIN” and “OUTER JOIN”?
    Get first key in a (possibly) associative array?
    Should I use field 'datetime' or 'timestamp'?

Instagram

About

Popular Posts

  • How to get Real IP,ISP,Country,City and etc from Visitor using PHP
    How to get Real IP,ISP,Country,City and etc from Visitor using PHP Php Get Real visiter's IP and ISP and Country and City and Countr...
  • How to efficiently iterate over each Entry in a Map?
    How to efficiently iterate over each Entry in a Map? If I have an object implementing the  Map  interface in Java and I wish to iterate...
  • In Java, difference between default, public, protected, and private
    In Java, difference between default, public, protected, and private In Java , are there clear rules on when to use each of access modifi...
  • How to check if PHP array is associative or sequential?
    How to check if PHP array is associative or sequential? PHP treats all arrays as associative, so there aren't any built in function...
  • Length of a JavaScript object
    Length of a JavaScript object If I have a JavaScript object, say var myObject = new Object (); myObject [ "firstname" ] ...
  • How to return the date part only from a SQL Server datetime datatype
    How to return the date part only from a SQL Server datetime datatype SELECT GETDATE () Returns:  2008-09-22 15:24:13.790 I want tha...
  • Does finally always execute in Java?
    Does finally always execute in Java? I have a try/catch block with  return s inside it. Will the finally block be called? For example...
  • What is the difference between “INNER JOIN” and “OUTER JOIN”?
    What is the difference between “INNER JOIN” and “OUTER JOIN”? Also how do  LEFT JOIN ,  RIGHT JOIN  and  FULL JOIN  fit in? Answ...
  • Get first key in a (possibly) associative array?
    Get first key in a (possibly) associative array? What's the best way to determine the first key in a possibly associative array? My...
  • Should I use field 'datetime' or 'timestamp'?
    Should I use field 'datetime' or 'timestamp'? Would you suggest using a datetime or a timestamp field, and why (using M...

statcounter



statcounter



Template Created By ThemeXpose & Blogger Templates