CASE in ORDER BY statement

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.

Leave a Comment

Comments

  • Gregory Narain | October 8, 2004

    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

  • Dave Carabetta | October 8, 2004

    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!

  • Mark | October 8, 2004

    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)

  • neil | October 8, 2004

    i haven’t got access to Oracle anymore but couldn’t you use decode() as well ?

  • neil | October 8, 2004

    i haven’t got access to Oracle anymore but couldn’t you use decode() as well ?

  • Alex Sherwood | October 8, 2004

    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.

  • Qasim Rasheed | October 8, 2004

    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

  • Dave Carabetta | October 9, 2004

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

  • Johnny Boursiquot | October 9, 2004

    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.

  • Mark | November 21, 2004

    Would this work on MS sql or MS access? Or is CASE just supported in Oracle?

  • Qasim Rasheed | November 21, 2004

    AFAIK, Case statements are supported in SQL Server, however I am not sure about Access

  • Mark Lyczewski | April 22, 2006

    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,

  • Andreas | July 1, 2006

    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 ?

  • Umar | July 18, 2006

    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.

  • Brian Griffin | August 2, 2006

    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.

  • Ken Mercadante | December 15, 2006

    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.

  • SF | January 9, 2007

    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?

  • SS | March 21, 2007

    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?

  • The Mikeness | April 30, 2007

    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.

  • mlw | May 4, 2007

    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

  • SF | August 7, 2007

    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) = ‘&quot;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) = ‘&quot;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…

  • Chacham | November 15, 2007

    To: The Mikeness

    To get it backwards, multiply the numbers by -1, such as: CASE Order_By WHEN ASC THEN 1 ELSE -1.

  • Michael | September 29, 2009

    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

  • lakshminarayana | December 11, 2009

    really good stuff related to order by clause.

  • Deepak Dakhore | January 25, 2010

    i am BE computer science engineer

  • Deepak Dakhore | January 26, 2010

    how i use order by clause in dynamic sql query
    i want to pass bind variable for order by clause

  • Garth Ogle | October 27, 2010

    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.

  • Puneet | January 10, 2012

    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

  • deepak | March 14, 2012

    select *
    from
    apps.po_headers_all
    order by
    case
    WHEN approved_date IS NULL then
    creation_date
    else
    approved_date
    end DESC

  • Joe Simes | March 23, 2017

    This works too:

    SELECT *
    FROM tbl_document
    ORDER BY NVL (document_releaseDate, document_createdDate) DESC;