SQL Ordering and Grouping

9th May 2017

You can sort data retrieved from the database using SQL ordering and grouping queries. Ordering is most commonly used when wanted to sort records by a specific column in ascending or descending order. For example, if a database table has a ‘created_at’ column, you can order by this column to find out the most recent data records added to the table.

The ORDER BY keyword is used to achieve this. This is followed by a single column name, or a comma separated list of column names to order by.

For example, to order by a ‘created_at’ column, you can write the following.

SELECT * FROM table_name ORDER BY created_at;

By default, ORDER BY will sort the records in ascending order. You can use the ASC or DESC keyword to sort the records in ascending or descending order.

SELECT * FROM table_name ORDER BY created_at DESC;

If sorting by multiple columns, the SQL query will firstly order by the first column, and then the next column, and so on.

SELECT * FROM table_name ORDER BY created_at DESC, updated_at ASC;

SQL GROUP BY is used alongside a SELECT statement with aggregator functions (COUNT, SUM, MIN, MAX) to arrange data into groups. The keyword is especially useful when dealing with records that have duplicate values within them.

For example, consider the following scenario: You have an ’employees’ table with multiple employee records with an ’employee_name’ (assume that this column is just for the employee’s first name) and ‘salary’ column.

As there is a chance that multiple employees have the same name, let’s assume you want to query the total salary amount of the employees.

A simple SELECT * FROM employees query returns the following information.

Now there are two employees named ‘John’ and three named ‘Alice’. Using the GROUP BY clause and selecting the SUM of the salary column will result in the following.

SELECT `employee_name`, SUM(`salary`) FROM employees GROUP BY `employee_name`;

You’ll note that only one instance of ‘John’ and ‘Alice’ have been returned, and the duplicate employee names’ salaries have been added together.

The ORDER BY and GROUP BY clauses can be extremely useful when dealing with large record sets as it will help return data in the arranged format that the user requested.