Sorting Data – the ORDER BY Clause

Randy Sims SQL Fundamentals I

Exam Topic: Restricting and Sorting Data – Sort the rows that are retrieved by a query

As data is inserted and updated and indexes keep track of rows, the order in which the rows are stored can change. There is no guarantee that the way Oracle stores the data in your system is in any kind of order. For this reason, the ORDER BY clause is used to return data in the order you want it returned. Data can be sorted in any way you can imagine if you know the tools and capabilities of the ORDER BY clause.

Sorting Keywords and Behaviors

A couple of keywords will provide the sorting order you want but you also have to know what to expect from certain datatypes. If it comes out different than how you were picturing it, likely you are looking at an issue with data types or in designating the order how you want it.

ASCending and DESCending

There are two directions in which any data can be sorted: ascending and descending. If a direction isn’t listed, ASCending is the default.

ORDER BY column1;

ORDER BY column1 DESC;

ORDER BY column1 ASC;

NULLS LAST or NULLS FIRST

By default, NULL values are ordered as the last value when the order is ASC or first if DESC. Adding NULLS LAST or NULLS FIRST can change this default behavior so you can have your nulls wherever you want them.

SQL> set null *NULL*
SQL> column state format a10
SQL> select distinct state from tbl_locations order by state;

STATE
----------
FL
GA
*NULL*

SQL> select distinct state from tbl_locations order by state desc;

STATE
----------
*NULL*
GA
FL

SQL> select distinct state from tbl_locations order by state nulls first;

STATE
----------
*NULL*
FL
GA

SQL> select distinct state from tbl_locations order by state desc nulls last;

STATE
----------
GA
FL
*NULL*

Alphanumeric Data

With alphabetical or alphanumeric data, ordering is done by from 0 to 9 and A to Z, character by character from left to right. Ascending order will sort from 0 to 9 and A to Z, descending order will sort from Z to A and 9 to 0.

Here is a list of alphanumeric data sorted ASC:

504
5049AA
504BY
504BZ
504CZ
504DT
504GA
504GB
504HA
504HB
504MA
504PA

…and here it is descending.

504PA
504MA
504HB
504HA
504GB
504GA
504DT
504CZ
504BZ
504BY
5049AA
504

Numeric Data

The order of strictly numeric data is as you would think it would be. 0 to 9 for ascending and 9 to 0 for descending.

1
2
3

Alphanumeric Data that Looks Like Numeric Data

Sometimes, numeric data is stored as alphanumeric data. When that happens, sorting follows the rules of alphanumeric data instead of numeric data.

... order by alpha_id;

ALPH
----
1
10
12
14
16
18
2
20
4
6
8

You can convert the alphanumeric data to a number in the ORDER BY clause, but only if ALL data in the column can be converted to a number.

order by to_number(alpha_id);

ALPH
----
1
2
4
6
8
10
12
14
16
18
20

ORDER BY Column Names

The most basic method of sorting your columns is by name. You saw some of that above. To order by a column name, list the columns you wish to sort by.

SELECT state, zip_code
FROM tbl_locations
ORDER BY state, zip_code;

ORDER BY Column Positions

The other basic method of ordering iis to refer to the position of the column to sort by in the SELECT clause. The query below will sort the returned data by zip_code because that is the column in the 2nd position. The data will be returned in DESCending order.

SELECT state, zip_code
FROM tbl_locations
ORDER BY 2 DESC;

ORDER BY Column Aliases

The ORDER BY clause is the only clause that can use aliases from the SELECT clause. If you have an expression in your SELECT list with an alias, you can use that alias in the ORDER BY clause.

SELECT state, 'ZIP: ' || zip_code as "ZIP"
FROM tbl_locations
ORDER BY ZIP;

Composite Sorting

Composite sorting is the term used to describe more than one expression or value used to sort the data. It can combine any number of techniques: sorting by position, sorting by column name, sorting by aliases, one column ASCending, another column DESCending, and even using expressions.

SELECT alpha_id
FROM tbl_codes
ORDER BY CASE WHEN length(alpha_id)=1 THEN '0' || alpha_id ELSE alpha_id END;

SELECT city, state, zip_code, zip_code || '-0000' zip10
FROM tbl_locations
ORDER BY city DESC, state NULLS FIRST, zip10 ASC;

Make that ORDER BY clause work for you! In most cases, what you want will be easy enough, but remember all the tools available to get the sorting you want.

Tagged: ORDER BY