(For more resources on similar content, see here.)

If all you need in a SELECT query is ordering all the data in a table by just one of its column, all you need to do is putting the column in the query’s ORDER BY clause.

SELECT TITLE, ISBN, PUBLISH_DATE
FROM BOOK
ORDER BY ISBN DESC

What if you need to order by more than column, i.e. hierarchical ordering? Again, just have all the ordering columns in the ORDER BY. You need to sequence them to command the rank of ordering: The first column is the primary; the second column, secondary; and so forth, next columns down the ordering rank.

SELECT TITLE, ISBN, PUBLISH_DATE
FROM BOOK
ORDER BY TITLE, ISBN, PUBLISH_DATE DESC

But, what if the PUBLISH_DATE’s data format is MM-YYYY-DD? Which one of 12-2010-20, 12-2009-30, and 10-2010-01, is higher or lower than the others? This format is not good for ordering, as you need to order the dates by their year, then by month, and lastly by date.

This article shows how to solve various ordering cases in SELECT queries.

Derived

Let’s first have a look at the SELECT query for solving the PUBLISH_DATE ordering. (All SELECT queries in this article are tested to run correctly in Oracle database.)

If you run:

SELECT PUBLISH_DATE
FROM BOOK
ORDER BY PUBLISH_DATE DESC

On the following publish dates:

Its query output is:

While we expect its output should be:

We need to order by year (YYYY), then by month (MM), and lastly by date (DD).

SELECT * FROM BOOK
ORDER BY SUBSTR(PUBLISH_DATE, 4, 4) DESC, 
SUBSTR(PUBLISH_DATE, 1, 2) DESC, SUBSTR(PUBLISH_DATE, 8,2) 
DESC

This kind of derived ‘column’ for ordering can be applied to other than date.

Translated

What if the data format of the PUBLISH_DATE is MMM-YYYY-DD? The month is its first three characters name, not its number. Is then JAN-2009-01 higher or lower than APR-2010-01?

We need to translate the month name into number using a translation reference table:

Our SELECT query now becomes:

SELECT PUBLISH_DATE, MONTH_NAME, MONTH_NO 
FROM BOOK, MONTH_TRANSLATION_REF
WHERE SUBSTR(PUBLISH_DATE,1,3) = MONTH_NAME
ORDER BY SUBSTR(PUBLISH_DATE, 4, 4) DESC, MONTH_NO DESC, 
SUBSTR(PUBLISH_DATE, 8,2) DESC

If you run the query on the following publish dates:

The query output is:

You might have noticed that the query also applies the ‘derived’ ordering from the previous example.

The month translation reference table we use in this example is based on a common predefined convention, that Jan is first (1); February is second (2), and so on. You are not limited to this kind of table; you can set up any custom translation reference table to suit your ordering need.