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

Exclusive offer: get 50% off this eBook here
High Availability MySQL Cookbook

High Availability MySQL Cookbook — Save 50%

Over 60 simple but incredibly effective recipes focusing on different methods of achieving high availability for MySQL database

£16.99    £8.50
by Djoni Darmawikarta | August 2010 | Architecture & Analysis MySQL

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 will have virtually unlimited sorting flexibilities in your armory. This article by Djoni Darmawikarta shows how to solve various ordering cases in SELECT queries.

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

High Availability MySQL Cookbook Over 60 simple but incredibly effective recipes focusing on different methods of achieving high availability for MySQL database
Published: April 2010
eBook Price: £16.99
Book Price: £27.99
See more
Select your format and quantity:

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


MySQL 5.1 Plugin Development Extend MySQL to suit your needs with this unique guide into the world of MySQL plugins
Published: August 2010
eBook Price: £16.99
Book Price: £27.99
See more
Select your format and quantity:

About the Author :


Djoni Darmawikarta built his career in IBM Asia Pacific and Canada as a software engineer, international consultant, instructor and project manager, for a total of 17 years. He's currently a technical specialist in the Data Warehousing and Business Intelligence team of a Toronto-based insurance company. Outside of his office works, Djoni writes IT articles and books.

Books From Packt


Mastering phpMyAdmin 3.1 for Effective MySQL Management
Mastering phpMyAdmin 3.1 for Effective MySQL Management

Oracle SQL Developer 2.1
Oracle SQL Developer 2.1

MySQL Admin Cookbook
MySQL Admin Cookbook

Joomla! 1.5 JavaScript jQuery
Joomla! 1.5 JavaScript jQuery

MySQL for Python: Database Access Made Easy
MySQL for Python: Database Access Made Easy

Amazon SimpleDB Developer Guide
Amazon SimpleDB Developer Guide

Oracle Database 11g – Underground Advice for Database Administrators
Oracle Database 11g – Underground Advice for Database Administrators

Learning SQL Server 2008 Reporting Services
Learning SQL Server 2008 Reporting Services


Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software