Oracle – Change the behavior of ORDER BY
There are times when you would like to order the results of a query in such a way that it cannot be accomplished by the default behavior of the order by clause. For example, let’s look at the following table:
tbl_families
| FirstName | LastName | FamilyMember |
|---|---|---|
| Mark | Smith | child |
| John | Smith | husband |
| Susan | Smith | wife |
| Sophia | Dice | child |
| Craig | Dice | husband |
| Maria | Dice | wife |
Let’s say you need to order this table in such a way, so that you get all the husbands first, then the wives and the children at the end. Obviously, descending or ascending ordering by the FamilyMember field will not do the job. The solution is to combine the order by clause with the Oracle’s decode clause. Here is the result:
SELECT FirstName, LastName, FamilyMember
FROM tbl_families
ORDER BY DECODE (FamilyMember, 'husband', 'A', 'wife', 'B', 'child', 'C', 'Z');
No comments yet.
Leave a comment
Search
Archive
| M | T | W | T | F | S | S |
|---|---|---|---|---|---|---|
| « Mar | ||||||
| 1 | 2 | 3 | 4 | 5 | 6 | |
| 7 | 8 | 9 | 10 | 11 | 12 | 13 |
| 14 | 15 | 16 | 17 | 18 | 19 | 20 |
| 21 | 22 | 23 | 24 | 25 | 26 | 27 |
| 28 | 29 | 30 | 31 | |||
Recent Comments
- Aron on Clone Disk Drives with Ubuntu. Make an Exact Copy of Your Hard Drive.
- letroll on Android – Displaying Dialogs From Background Threads
- When Wireless Goes Rogue « Ham Radio Weblog PD0AC on KARMA on the Fon and Sniffing Wireless Network Traffic with Ubuntu – Step by Step
- jornando junior on Android – Displaying Dialogs From Background Threads
- Geo on Extract Audio (.mp3) from Video Files Like .flv, .mov, .avi and Others with Ubuntu
Categories
Blogroll
Online Tools
Other
BLOG ARCHIVE
- March 2012 (1)
- November 2011 (1)
- August 2011 (1)
- April 2011 (1)
- January 2011 (2)
- September 2010 (1)
- August 2010 (2)
- July 2010 (2)
- June 2010 (2)
- May 2010 (1)
- January 2010 (2)
- December 2009 (2)
- November 2009 (3)
- October 2009 (1)
- September 2009 (3)
- July 2009 (1)
- May 2009 (1)
- March 2009 (1)
- February 2009 (2)
- January 2009 (2)
- December 2008 (1)
- November 2008 (4)
- October 2008 (5)