There is More to the ORDER BY Clause than Sorting a Column

There’s more than one way to achieve high availability for MySQL and this Cookbook covers a range of techniques and tools in over 60 practical recipes. The only book of its kind, you’ll be learning the natural, engaging way.

(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.

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

Computed

While the foregoing cases are ordering on, or based on, a column, you might have a case where the ordering needs to be computed from row values. An example can be: to order firstly based on monthly number of books published, within year and month of the year. In other words, the column ordering hierarchy is: Year, "the number of books published within the month" of the year, and then the month of the year. The "monthly number of books published" is inter-rows computed value.

Our SELECT query to solve the case is:

SELECT YEAR, MONTH, COUNT(*)"NO PUBLISHED" FROM
(
SELECT SUBSTR(PUBLISH_DATE, 5, 4) YEAR,
SUBSTR(PUBLISH_DATE,1,3) MONTH, MONTH_NO
FROM BOOK, MONTH_TRANSLATION_REF
WHERE SUBSTR(PUBLISH_DATE,1,3) = MONTH_NAME
)
GROUP BY MONTH, MONTH_NO, YEAR
ORDER BY YEAR ASC, "NO PUBLISHED" DESC, MONTH ASC

Note that:

  • "The number of books published" computation is done by the COUNT function.
  • The inner query is for ordering the month (by month number).

If you run the query on the following data:

Its output is:

Your need can involve more complex computation, such as multiple tables.

Artificial

The last case in this article is to produce an output: year and followed by the months of the year.

The query to produce that output requires an artificial ordering column, ARTIFICIAL_ORD.

SELECT CATEGORY, PERIOD, CNT FROM
(SELECT CATEGORY, 1 MO, SUBSTR(PUBLISH_DATE, 5,4) YR,
SUBSTR(PUBLISH_DATE, 5,4) PERIOD ,COUNT(*) CNT, 1
ARTIFICIAL_ORD
FROM BOOK
GROUP BY CATEGORY, SUBSTR(PUBLISH_DATE, 5, 4)
UNION ALL
SELECT CATEGORY, MONTH_NO MO, SUBSTR(PUBLISH_DATE, 5, 4)
YR, SUBSTR(PUBLISH_DATE, 1, 3) PERIOD, COUNT(*) CNT, 2
ARTIFICIAL_ORD
FROM BOOK, MONTH_TRANSLATION_REF
WHERE SUBSTR(PUBLISH_DATE, 1, 3) = MONTH_NAME
GROUP BY CATEGORY, SUBSTR(PUBLISH_DATE, 5, 4), MONTH_NO,
SUBSTR(PUBLISH_DATE, 1, 3))
ORDER BY CATEGORY, YR, MO, ARTIFICIAL_ORD

The data on which the query produces the foregoing output is:

Summary

You can do more with the ORDER BY clause in a query than just putting in it a column or two. By mixing and matching the cases and solution examples in this article you have virtually unlimited sorting flexibilities in your armory.


Further resources on this subject:


Books to Consider

comments powered by Disqus