This was one of those things where I've just gone 'I wonder if this works..' and strangely enough it DID!
Situation was something like the following (okay – something is a very broad term, because it wasn't really like this at all) –
I have a document management system – and each document has a document_createdDate and a document_releaseDate – however if the user chose to release a document 'Now' the document_releaseDate would be set to NULL.
So when I went to order a document list by document_releaseDate, obviously that would be rather hard, as values could be NULL in a date list.
So I figured – why not sort such that, if is document_releaseDate is NULL, then sort by the document_createdDate – that would provide a able solution to my sorting issue.
Then I discovered I could put CASE statements into my ORDER BY clause – and that made things real easy – e.g.
select *
from
tbl_document
order by
case
WHEN document_releaseDate IS NULL then
document_createdDate
else
document_releaseDate
end DESC
And presto – a conditional sort! Of course there are other ways to solve the same problem, but I thought that was nifty.
This was done a Oracle 9i database.
Comments
I don’t do much with Oracle, but I know that there’s a function in T-SQL that would could us..
ORDER BY ISNULL(document_releaseDate,document_createdDate)
Maybe there’s something like that in PL-SQL
Yes, in Oracle you’d use NVL(document_releaseDate,document_createdDate).
That being said, I could see the use for this in other situations where you’re not testing for NULL values. This is a pretty slick tip!
Thanks Dave,
You hit it on the head.
There ARE other ways to test for NULL values, I just wanted to demonstrate that you could do conditional ordering – you could have made it
WHEN document_releaseDate < TO_DATE(’01/01/2000′, ‘DD/MM/YYYY’) if you had somehow wanted to use a different date if the release date was before the year 2000 … (don’t know why you would want to do that however).
Although I didn’t know about NVL (must have missed it!) so thank you for your tip ;o)
i haven’t got access to Oracle anymore but couldn’t you use decode() as well ?
i haven’t got access to Oracle anymore but couldn’t you use decode() as well ?
In fact, when you create an output column on the fly, perhaps with a case statement, you have to use the CASE statement in the order by clause, and NOT the alias that you assign to the column.
Same for GROUP BY.
One thing that I found useful while using case statement in order by clause is something like this
case when name = ‘Qasim’ then 2
when name = ‘Rasheed’ then 1
else 3
end
Neil, yes you can use DECODE() if you want. However, Oracle 9i and higher support the CASE statement, which is much more readable code, in my experience. Alas, our production DB is still on 8i, so I’m stuck with DECODE().
That’s pretty nifty. But I must say that you could have avoided all this by simply having the release data be the same as the create date in the case where the user wants it released now.
There are perhaps other reasons why you might not have taken that approach, but I myself have had similar cases where the easiest way to handle them is to simply make the dates the same.
Just a thought.
Would this work on MS sql or MS access? Or is CASE just supported in Oracle?
AFAIK, Case statements are supported in SQL Server, however I am not sure about Access
you should not use ISNULL or NVL as these are vendor specific extensions to the SQL language. The proper (and portable from system to system) method is to use the COALESCE function. Your select would look as follows:
select *
from
tbl_document
order by
coalesce(document_releaseDate, document_createdDate)
Coalesce will choose the first non-null value in your list
.. also works great in your select statements,
Here’s another twist :
order by a.kpmseg2 asc, (CASE when a.sectop in(‘S5T01′,’S5T02′,’S5T03′,’S5C16′,’S5C17′,’S5C18′,’S5C19′,’S5C20′,’SAO01′,’SMA01’) then a.master ELSE a.ppaen1 END) DESC
Anyone know how to make the desc/asc keyword conditional as well ?
I also have similar situation and I solve it by following logic
order by decode(upper(aiv_fieldorder), gvc_asc,
decode(aiv_orderfield, to_number(‘4′,’999999999.99’), b.total_qty, ‘2’, a.bulk_acct,
‘3’, a.trade_type, to_number(‘5′,’999999999.99’), a.price, ‘6’, a.inst_desc, ‘7’, a.symbol,
‘8’, a.iacode, ‘9’, c.status, ’10’, a.security_no, to_char(b.initiated_date,’yyyymmdd’))),
decode(upper(aiv_fieldorder), gvc_desc,
decode(aiv_orderfield, to_number(‘4′,’999999999.99’), b.total_qty, ‘2’, a.bulk_acct,
‘3’, a.trade_type, to_number(‘5′,’999999999.99’), a.price, ‘6’, a.inst_desc, ‘7’, a.symbol,
‘8’, a.iacode, ‘9’, c.status, ’10’, a.security_no, to_char(b.initiated_date,’yyyymmdd’))) desc
This way you can also choose ASC and DESC conditionally.
Using CASE seems to require that conditions in the WHERE clause are all of the same datatype. When using DECODE() you do not seem to be governed by this restriction.
As for ASC and DESC, I have implemented dynamic sorting like this:
SELECT t1.*
FROM (SELECT ID, Name FROM tblTest ORDER BY DECODE(1, 1, Name, 2, ID, Name)) t1
ORDER BY ROWNUM * 1
In the outermost ORDER BY, note the statement ROWNUM * 1. this will cause an ASC sort. By changing the statement to ROWNUM * -1 you will get a DESC sort.
I noticed one caveat to using the CASE statement for conditional sorting in T-SQL: Both fields being sorted must be the same data type. I tried an experiment where a user-preferred sort order gave them the option of sorting a list alphabetically or by due date, and T-SQL kept trying to convert the string field to an integer. I’m not sure if Oracle behaves the same way, but it would useful to know. If the optional fields are the same data type the CASE statement works fine.
Here is a related issue:
I am using the order by case when scenario to ignore articles (the, a) in my titles column in a database of articles. So far, I’m not finding something that works. Here is my broken code —
<cfquery datasource="…" name="…">
SELECT Title, Date_Published
FROM Articles
ORDER BY
CASE WHEN LEFT(Title,4) = ‘The ‘ THEN SUBSTRING(Title,4,LEN(Title)-4), Date_Published
ELSE Title, Date_Published
END
</cfquery>
Any suggestions?
ORDER BY CASE WHEN i_sort_by = ‘SUM’ THEN SUM
ELSE TITLE END
Any suggestions if you need to sort by number or by varchar2 ?column?
Just wondering, i tried something like this:
order by
case
when &&sort=1 then 1
when &&sort=2 then 2 desc
when &&sort=3 then 3 desc
when &&sort=4 then 4 desc
when &&sort=5 then 5
when &&sort=6 then 6
else 2 desc end
;
in conjunction with an SQLPlus ACCEPT variable. there doesnt seem to be a way to do manually defined desc and asc clauses with the case like this as far as I know. does anyone know another way to do this in Oracle short of PL/SQL?
Oh and btw, incase some people didnt know, you can use the coalesce function to coalesce thru several columns/values, it matches to the first entry that is not null.
select coalesce(null,null,null,null,null,2,1,3,null) from dual;
would return 2, as it is the first non-null. this may help instead of using nvl or nvl2 for those of you using Oracle 10g.
re: conditional sorting when sort columns are of different type …
I saw a solution somewhere. Instead of writing a single statement to return one of two columns, write two case statements, each of which returns one of the values or null. You are sorting on two columns instead of one, but you get the desired effect because the "other" values will all be the same (null).
e.g. say name is a character field and date is a date field…
… order by
case when {name sort is desired} then name end,
case when {date sort is desired} then date end
…
Well here is the solution I was actually looking for:
<cfquery datasource="#request.DSN#" name="titlesall">
SELECT Title
CASE
WHEN SUBSTRC(Title,0,4) = ‘The ‘ THEN SUBSTR(Title,5,LENGTH (Title)-4)
WHEN SUBSTRC(Title,0,5) = ‘"The ‘ THEN SUBSTR(Title,6,LENGTH(Title)-5)
WHEN SUBSTRC(Title,0,2) = ‘A ‘ THEN SUBSTR(Title,3,LENGTH(Title)-2)
ELSE title
END newtitle
FROM Databasename
ORDER BY
CASE
WHEN SUBSTRC(Title,0,4) = ‘The ‘ THEN SUBSTR(Title,5,LENGTH (Title)-4)
WHEN SUBSTRC(Title,0,5) = ‘"The ‘ THEN SUBSTR(Title,6,LENGTH(Title)-5)
WHEN SUBSTRC(Title,0,2) = ‘A ‘ THEN SUBSTR(Title,3,LENGTH(Title)-2)
ELSE title
END
</cfquery>
This outputs —
Addiction…
Asian Medical…
Bioethics…
The Body and…
Childbirth…
Ciencia…
Colonial…
The Cultures of…
To: The Mikeness
To get it backwards, multiply the numbers by -1, such as: CASE Order_By WHEN ASC THEN 1 ELSE -1.
I struggled with this yesterday. It seems ASC/DESC cannot be within the case statement. So my solution is:
CREATE FUNCTION my_function(
in_sort IN BINARY_INTEGER DEFAULT 0
…
ORDER BY CASE WHEN in_sort<>0 THEN primary_sort_column END, CASE WHEN in_sort=0 THEN primary_sort_column END DESC, secondary_sort_column
really good stuff related to order by clause.
i am BE computer science engineer
how i use order by clause in dynamic sql query
i want to pass bind variable for order by clause
Deepak,
You must use ‘execute immediate’: if you do so you can simply create the SQL statement from a string (literal or variable) and thus you can insert whatever order by clause you want without all of these shenanigans.
Hi Guys,
Can you please explain how CASE statement works in ORDER BY clause? I mean how and why it orders the ways it is doing.
Thanks
Puneet
select *
from
apps.po_headers_all
order by
case
WHEN approved_date IS NULL then
creation_date
else
approved_date
end DESC
This works too:
SELECT *
FROM tbl_document
ORDER BY NVL (document_releaseDate, document_createdDate) DESC;