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.