Ascending and Descending Orders

You can use the ASC and DESC keywords to specify ascending (smallest value first) or descending (largest value first) order.

The default order is ascending. For DATE and DATETIME data types, smallest means earliest in time and largest means latest in time. For character data types in the default locale, the order is the ASCII collating sequence, as listed in Collating Order for U.S. English Data.

For NCHAR or NVARCHAR data types, the localized collating order of the current session is used, if that is different from the code set order. For more information about collation, see SET COLLATION statement.

If you specify the ORDER BY clause, NULL values by default are ordered as less than values that are not NULL. Using the ASC order, a NULL value comes before any non-NULL value; using DESC order, the NULL comes last.

Specifying the order of NULL values

The ORDER BY clause can include the NULLS FIRST keywords or the NULLS LAST keywords to show explicitly (or else to override) the default sort order of NULL values:
  • The NULLS FIRST keywords instruct the database server to put NULL values first in the sorted query results. In an ascending sort, the ASC NULLS FIRST keywords request the default order. In a descending sort, DESC NULLS FIRST specifies that rows with a NULL value in the sort key column precede non-NULL rows in the sorted result set.
  • The NULLS LAST keywords instruct the database server to put NULL values last in the sorted query results. In a descending sort, the DESC NULLS LAST keywords request the default order. In an ascending sort, ASC NULLS LAST specifies that rows with a NULL value in the sort key column follow non-NULL rows in the sorted result set.