MySQueaL Resources

SELECT - Ordering results Statement

The results of a SELECT statement, by default, is displayed in the order in which the rows of data are found in the table, which may be the order in which they were entered into the table. To change the order of a results set, use the ORDER BY clause. As a basis for ordering the results, list one or more columns separated by commas. The order in which columns are listed is the order in which sorts will be conducted. You can also use aliases for columns, column combinations, or expressions that were established earlier in the same SELECT statement. Instead of stating a column's name, you can also state its position, where a value of 1 represents the first column in the table.

Here is an example of a SELECT statement using the ORDER BY clause:

SELECT CONCAT(name_first, ' ', name_last) AS Name,
MONTH(birth_date) AS 'Birth Month', email_address
FROM employees
ORDER BY 'Birth Month' ASC, Name ASC;

Here a list of employees, the month in which they were born, and their email addresses are extracted. For the name, the CONCAT( ) function is used to put the first and last name together, separated by a space. The AS clause establishes an alias of Name. The MONTH( ) function is used to extract the month from the birth_date column and the AS clause sets up the alias Birth Month. In the ORDER BY clause, the alias for the birth date is used for the initial sort and the name is used for the secondary sort. The result will be that all of the employees who have a birth date in the same month will be listed together and in alphabetical order by name. Both aliases are followed by the ASC keyword to indicate that the results should be sorted in ascending order. This is unnecessary, as ascending order is the default. However, to change an ordering method to descending, use the DESC keyword.

You can also order the results using expressions, which may be based on columns or aliases. Here is an example of a SELECT statement using an expression for ordering:

SELECT CONCAT(name_first, ' ', name_last) AS name,
pay_rate, hours
FROM employees
ORDER BY pay_rate * hours DESC;

In this example, the first and last names are selected and concatenated together under the name column heading in the results set. The pay_rate column lists the hourly dollar rate an employee is paid and the hours column contains the typical number of hours a week that an employee works. In the ORDER BY clause, the product of the hourly pay rate multiplied by the number of hours is determined for the ordering of the results set. The rows are to be listed in descending order per the DESC keyword based on the expression.