MySQL Order By

MySQL: ORDER BY Clause

ORDER BY clause with syntax and examples.

Description

The MySQL ORDER BY clause is used to sort the records in your result set.

Syntax

The syntax for the MySQL ORDER BY clause is:

SELECT expressions
FROM tables
WHERE conditions
ORDER BY expression [ ASC | DESC ];

Parameters or Arguments

expressions are the columns or calculations that you wish to retrieve.

tables are the tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.

conditions are conditions that must be met for the records to be selected.

ASC is optional. It sorts the result set in ascending order by expression (default, if no modifier is provider).

DESC is optional. It sorts the result set in descending order by expression.

Note

  • If the ASC or DESC modifier is not provided in the ORDER BY clause, the results will be sorted by expression in ascending order (which is equivalent to "ORDER BY expression ASC").
  • The ORDER BY clause can be used in a SELECT statement,SELECT LIMIT statement, and DELETE LIMIT statement in MySQL.
Example – Sorting without using ASC/DESC attribute

The MySQL ORDER BY clause can be used without specifying the ASC or DESC modifier. When this attribute is omitted from the ORDER BY clause, the sort order is defaulted to ASC or ascending order.

For example:

 SELECT salary
FROM emp
WHERE empid >=1000
ORDER BY salary;

This MySQL ORDER BY example would return all records sorted by the city field in ascending order and would be equivalent to the following ORDER BY clause:

SELECT salary
FROM emp
WHERE empid >=1000
ORDER BY salary ASC

Most programmers omit the ASC attribute if sorting in ascending order.

Example – Sorting in descending order

When sorting your result set in descending order, you use the DESC attribute in your ORDER BY clause as follows:

SELECT salary
FROM emp
WHERE empid >=1000
ORDER BY salary DESC

This MySQL ORDER BY example would return all records sorted by the city field in descending order.

Example – Sorting by relative position

You can also use the MySQL ORDER BY clause to sort by relative position in the result set, where the first field in the result set is 1. The next field is 2, and so on.

For example:

SELECT salary, empid, email
FROM emp
WHERE empid >=1000
ORDER BY 3 DESC

This MySQL ORDER BY would return all records sorted by the city field in descending order, since the city field is in position #3 in the result set and would be equivalent to the following ORDER BY clause:

SELECT salary, empid, email
FROM emp
WHERE salary >=1000
ORDER BY salary DESC ;

Admin has written 171 articles

Leave a Reply