SQL - ORDER BY & GROUP BY

ORDER BY Clause:

The SQL ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. Some database sorts query results in ascending order by default.

Syntax:

SELECT column-list

FROM table_name

[WHERE condition]

[ORDER BY column1, column2, .. columnN] [ASC | DESC];

You can use more than one column in the ORDER BY clause. Make sure whatever column you are using to sort, that column should be in column-list.

Example:

Consider the CUSTOMERS table having the following records:

Following is an example, which would sort the result in ascending order by LASTNAME and AGE:

SELECT * FROM CUSTOMER

     ORDER BY lastname, age;

Following is an example, which would sort the result in descending order by LASTNAME:

SELECT * FROM CUSTOMER

     ORDER BY lastname DESC;

Group By:

·         The SQL GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups.

·         The GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.

Syntax:

The GROUP BY clause must follow the conditions in the WHERE clause and must precede the ORDER BY clause if one is used.

SELECT column1, column2

FROM table_name

WHERE [ conditions ]

GROUP BY column1, column2

ORDER BY column1, column2

Example:

Consider the CUSTOMERS table is having the following records:

If you want to know the age  of each customer, then GROUP BY query would be as follows:

SELECT lastname, SUM(age) FROM CUSTOMER

     GROUP BY lastname;