SQL Server ORDER BY operator

Preamble

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

The DESC attribute in the ORDER BY clause is used to sort a set of results in descending order. Let’s look more closely.
In this illustration, we have a table with the information below:

Type the following SQL query:

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

There will be five records chosen. These are the outcomes you ought to obtain.

In this case, the field sup_id will be used to sort the results 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 if the first field in the result set is f1, the second is f2, the third is f3, etc.
In this illustration, a table of products contains the information below:

Enter the subsequent SQL query now:

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

There will be six records chosen. These are the outcomes you ought to obtain.

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

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

Using ASC and DESC Attributes

You can use both the ASC and DESC attributes in a single SELECT statement when using the ORDER BY SQL clause to sort a result set.
Let’s use the same product table from the previous example for this one.

Enter the subsequent SQL query now:

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

There will be six records chosen. These are the outcomes you ought to obtain.

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

Examples:

All examples will be listed in this table, unless otherwise stated:

Example №1
Let’s pull up the entire table of records and arrange them by age:

SELECT * FROM workers_1 WHERE id>0 ORDER BY age

The strings will be chosen by the SQL query in the following order:

All records have been chosen, so the WHERE block may not be necessary:

SELECT * FROM workers_1 ORDER BY f_age

The outcome won’t change if you explicitly specify the sort type, which is ASC:

SELECT * FROM workers_1 ORDER BY f_age ASC

Example №2
The records will now be sorted by decreasing age:

SELECT * FROM workers_1 ORDER BY f_age DESC

The strings will be chosen by the SQL query in the following order:

Example №3
Now let’s simultaneously sort the records by age and wage.

Records that have the same age (in this case, 23) will be found in the descending wages after the records are first sorted by age:

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

The strings will be chosen by the SQL query in the following order:

Example №4
Let’s sort by salary increase under the same conditions (i.e., first sorting by f_age).

The first and second entries will now be switched so that the f_sal is lower initially and then higher later:

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

The strings in the following order will be chosen by the SQL query:

Sql Training Online Sql Order By Sorting

About Enteros

​​​​Enteros offers a patented database performance management SaaS platform. It finds the root causes of complex database scalability and performance problems that affect business across a growing number of cloud, RDBMS, NoSQL, and machine learning database platforms.