Home > Articles > Data > SQL

Sorting Retrieved Data in SQL

  • Print
  • + Share This
Learn how to sort retrieved data in SQL using the SELECT statement's ORDER BY clause. This clause, which must be the last in the SELECT statement, can be used to sort data on one or more columns as needed.
This chapter is excerpted from STY SQL in 10 Minutes, Second Edition.
From the author of

In this lesson, you will learn how to use the SELECT statement's ORDER BY clause to sort retrieved data as needed.

Sorting Data

As you learned in the last lesson, the following SQL statement returns a single column from a database table. But look at the output. The data appears to be displayed in no particular order at all.

Input:

SELECT prod_name
FROM Products;

Output:

prod_name
--------------------
Fish bean bag toy
Bird bean bag toy
Rabbit bean bag toy
8 inch teddy bear
12 inch teddy bear
18 inch teddy bear
Raggedy Ann
King doll
Queen doll

Actually, the retrieved data is not displayed in a mere random order. If unsorted, data will be displayed in the order in which it appears in the underlying tables. This could be the order in which the data was added to the tables initially. However, if data was subsequently updated or deleted, the order will be affected by how the DBMS reuses reclaimed storage space. The end result is that you cannot (and should not) rely on the sort order if you do not explicitly control it. Relational database design theory states that the sequence of retrieved data cannot be assumed to have significance if ordering was not explicitly specified.

NOTE

Clause SQL statements are made up of clauses, some required and some optional. A clause usually consists of a keyword and supplied data. An example of this is the SELECT statement's FROM clause, which you saw in the last lesson.

To explicitly sort data retrieved using a SELECT statement, the ORDER BY clause is used. ORDER BY takes the name of one or more columns by which to sort the output. Look at the following example:

Input:

SELECT prod_name
FROM Products
ORDER BY prod_name;

This statement is identical to the earlier statement, except it also specifies an ORDER BY clause instructing the Database Management System software to sort the data by the prod_name column. The results are as follows:

Output:

prod_name
--------------------
12 inch teddy bear
18 inch teddy bear
8 inch teddy bear
Bird bean bag toy
Fish bean bag toy
King doll
Queen doll
Rabbit bean bag toy
Raggedy Ann

CAUTION

Position of ORDER BY Clause When specifying an ORDER BY clause, be sure that it is the last clause in your SELECT statement. If it is not the last clause, an error will be generated.

TIP

Sorting by Nonselected Columns Although more often than not the columns used in an ORDER BY clause will be ones selected for display, this is actually not required. It is perfectly legal to sort data by a column that is not retrieved.

  • + Share This
  • 🔖 Save To Your Account