[ Send this article ]
Your message has been sent.
How would you like to send this article:
[ Save this article ]
Save this article to your account for easy access.
This article has been saved to your account.
Go to my account
This article has been emailed to your Kindle.
Send this article
Complete the form below to send this article, There is More to the ORDER BY Clause than Sorting a Column, to a friend (or to yourself). We will never share your details (or your friend's) with anyone. For more information, read our Privacy Policy.
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.
(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:
- Data Modeling Naming Standards with IBM InfoSphere Data Architect [Article]
- Database/Data Model Round-Trip Engineering with MySQL [Article]
- Visual MySQL Database Design in MySQL Workbench [Article]
- Creating a View with MySQL Query Browser [Article]
- Building Queries Visually in MySQL Query Browser [Article]
- Solving Many-to-Many Relationship in Dimensional Modeling [Article]
- Normalizing Dimensional Model [Article]
- Comparing Cursor and Set Approaches in Processing Relational Data [Article]
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
|
|
Post new comment