SQL Server ORDER BY operator

SQL Server operator ORDER BY is used to sort records in the SELECT query result set.

Syntax for ORDER BY statement in SQL

Sorting by one field:

SELECT * FROM tab n WHERE condition ORDER BY F1

You can sort by many fields, not one at a time:

SELECT * FROM tab n WHERE condition ORDER BY F1, F2...

By default, records are sorted in ascending order to be sorted in descending order – set DESC:

SELECT * FROM tab n WHERE condition ORDER BY F DESC

By default the sorting will be as if ASC was supplied:

SELECT * FROM tab n WHERE condition ORDER BY F ASC

A condition where it is not necessary – if it is not set, all records will be selected:

SELECT * FROM tab n ORDER BY F

Overall view:

SELECT list
  FROM tab
  [WHERE conditions]
  ORDER BY list [ ASC | DESC ];

where:

• list – Columns or calculations that you want to get
• tab – The tables from which you want the records. The FROM sentence must contain at least one table
• WHERE conditions – optional. Conditions to be met for entries to be selected
• ASC – optional. ASC sorts the result set in ascending order. Default behavior if not specified.
• DESC – optional. DESC sorts the result set in descending order.

Note:
When ASC or DESC is not used in the ORDER BY, the results will be ordered in ascending order. This is same as ORDER BY ASC.

Sorting results in ascending order

To sort the results in ascending order, you can specify the ASC attribute. If no value (ASC or DESC) is specified after the field in the ORDER BY sentence, the sort order will be in ascending order by default. Let’s look at this further.
In this example, we have a table with the following data:

Enter the following SQL statement:

SELECT *
   FROM cust
  ORDER BY l_name;

Six records will be selected. Here are the results that you should get.

In this example, all records from the customer table will be returned, sorted by l_name field in ascending order, and will be equivalent to the next ORDER BY SQL sentence.

SELECT *
   FROM cust
  ORDER BY l_name ASC;

Most programmers skip the ASC attribute when sorting in ascending order.

Sorting results in descending order

When sorting a set of results in descending order you use the DESC attribute in the ORDER BY sentence. Let us take a closer look.
In this example, we have a table with the following data:

Enter the following SQL statement:

SELECT *
   FROM sup
  WHERE sup_id > 40
  ORDER BY sup_id DESC;

Five records will be selected. Here are the results that you should get.

In this example, the set of results will be sorted by field sup_id in descending order.

Sorting results by relative position

You can also use the ORDER BY SQL statement to sort by relative position in the result set, where the first field in the result set is f1, the second f2, the third f3, etc.
In this example, we have a table of products with the following data:

Now enter the following SQL statement:

SELECT prod_id,
    prod_name
  FROM prod
  WHERE prod_name <> 'Bread'
 ORDER BY 1 DESC;

Six records will be selected. Here are the results that you should get.

In this example, the results are sorted by the prod_id field in descending order, since the prod_id field is at position #1 in the result set and will be equivalent to the next SQL statement ORDER BY.

SELECT prod_id,
    prod_name
  FROM prod
  WHERE prod_name <> 'Bread'
 ORDER BY prod_id DESC;

Using ASC and DESC Attributes

When sorting a result set using ORDER BY SQL sentence you can use ASC and DESC attributes in one SELECT statement.
In this example, let’s use the same table of products as in the previous example.

Now enter the following SQL statement:

SELECT *
    FROM prod
  WHERE prod_id <>
  ORDER BY cat_id DESC,
     prod_name ASC;

Six records will be selected. Here are the results that you should get.

In this example, the records are returned sorted by the cat_id field in descending order and the secondary sorting by the prod_name field in ascending order.

Examples:

All examples will be on this table unless otherwise stated:

Example №1
Let’s get all the records from the table and sort them by age:

SELECT * FROM workers_1 WHERE id>0 ORDER BY age

The SQL query will select the strings in the following order:

Since all records are selected, the WHERE block may not be specified:

SELECT * FROM workers_1 ORDER BY f_age

You can also specify the sort type explicitly – ASC – the result will not change:

SELECT * FROM workers_1 ORDER BY f_age ASC

Example №2
Let’s now sort the records by decreasing age:

SELECT * FROM workers_1 ORDER BY f_age DESC

The SQL query will select the strings in the following order:

Example №3
Let’s now sort the records by age and wage at the same time.

The records will be sorted by age first, and those records, where the age is the same (in our case – 23), will be located in the descending wages:

SELECT * FROM workers_1 WHERE id>0 ORDER BY f_age ASC, f_sal DESC

The SQL query will select the strings in the following order:

Example №4
Under the same conditions (i.e. first sorting by f_age), let us sort by salary increase.

Now the first and second entries will be swapped so that first the f_sal is lower and then the f_sal is higher:

SELECT * FROM workers_1 WHERE f_id>0 ORDER BY f_age ASC, f_sa DESC

The SQL query will select the strings in the following order:

Sql Training Online – Sql Order By – Sorting