Skip to main content

SELECT Statement: the ORDER BY clause

When executing an SQL SELECT command there is no guarantee in which order the data rows are returned. If you require the rows in a specific order (i.e. ascending or descending) then you need to use the ORDER BY clause of the SQL SELECT command. You can order by one or more columns, each of which can be ascending or descending.

SELECT column, column, ....

FROM tablename

WHERE column = <column value>

ORDER BY column [ASC | DESC], column [ASC | DESC], .... ;

For example:

  • List all employees in the pensions section. Return the results in ascending order of forename within ascending order of surname.

SELECT employee_number, forename, surname

FROM employee

WHERE section_name = 'Pensions'

ORDER BY surname, forename;

  • List all employees in the company. Return the results in descending order of salary within ascending order of section.

SELECT section_name, employee_number, forename, surname, salary

FROM employee

ORDER BY section_name, salary DESC;

Note: You can combine any of the SQL SELECT options described above, ie select specific rows and columns, sorted on specific columns.

SELECT forename, surname, salary

FROM employee

WHERE section_name = 'Pensions'

AND (surname = 'Jones' OR surname = 'Wong')

AND gender = 'F'

AND salary > 25000

ORDER BY surname, forename, salary DESC;

Note that the use of the brackets around the OR clause to ensure the command is executed correctly. Without those brackets the AND clauses would be carried out first, followed by the OR clause. That would mean that all rows containing a surname of Wong would always be returned, irrespective of the other selection criteria.

Next: Activity 2.1