Sunday, August 21, 2011

MySQL - Where, GroupBy, Having, OrderBy, Limit

Hierarchy of Conditions

  
         WHERE
       |
     GROUP BY
   /       \
 HAVING   ORDER BY
   \   /
   LIMIT


GROUP BY

When we issue a SELECT command we are shown the results in the order that the records were entered.

We can change this by using the 'GROUP BY' directive, allowing us to display the data grouped by field. To return the results ordered by artist:

mysql> SELECT * FROM cds
-> GROUP BY cds.artist;

HAVING

The HAVING condition is really just another WHERE condition that acts as a 'secondary constraint' on the result set. This works best when you are trying to apply a restrictive condition after a grouping has taken place.


SELECT T1.ID FROM TEST_MULTIPLE T1 WHERE T1.TITLE IN (SELECT T2.TITLE FROM TEST_MULTIPLE T2 GROUP BY T2.TITLE HAVING COUNT(T2.TITLE) > 1)

References -

http://www.keithjbrown.co.uk/vworks/mysql/mysql_p3.php

Extra Tip:

Insert multiple rows into a table in single insert query -


INSERT INTO customers(cust_name,
   cust_address,
   cust_city,
   cust_state,
   cust_zip,
   cust_country)
VALUES(
        'Pep E. LaPew',
        '100 Main Street',
        'Los Angeles',
        'CA',
        '90046',
        'USA'
     ),
      (
        'M. Martian',
        '42 Galaxy Way',
        'New York',
        'NY',
        '11213',
        'USA'
   );

No comments:

Post a Comment