Random sorting with ORDER BY with CASE clause

Question

I am testing ORDER BY clause with CASE, and came across this problem.

My test select statement:

SELECT to_date as "One", field1 as "Two"
   FROM(
        SELECT to_date('yyyy-mm-dd', '2017-10-10'), '333' union all
        SELECT to_date('yyyy-mm-dd', '2017-09-09'), '111' union all
        SELECT to_date('yyyy-mm-dd', '2017-09-09'), '222' union all
        SELECT to_date('yyyy-mm-dd', '2017-09-09'), '' union all
        SELECT to_date('yyyy-mm-dd', '2017-09-09'), ''
        )
   ORDER BY One DESC,
          CASE when Two = '' then 1 
               else 0 end DESC

And it's result may vary in a way, that sorting by second column is random:

How should I modify CASE clause to avoid it?

@YogeshSharma my bad, sorry – bobryash Nov 06 '17 at 11:34 — bobryash, Nov 06 '17 at 11:34

MT0 · Answer 1 · 2017-11-06T11:56:38.387

In Oracle, an empty string '' is the identical to NULL so your query is:

ORDER BY
  One DESC,
  CASE when Two = NULL then 1 else 0 end DESC

When comparing values, the are possible states are:

Equality                 Result
------------------------ ------
value = value            TRUE
value = other_value      FALSE
value = NULL             NULL
NULL  = NULL             NULL

Your CASE expression will only evaluate to 1 when the equality evaluates to TRUE and this will never be the result when at least one side of the equality is NULL.

What you want is to use IS NULL rather than = '':

ORDER BY
  One DESC,
  CASE WHEN Two IS NULL THEN 1 ELSE 0 END DESC,
  Two DESC;

Which you can simplify to:

ORDER BY
  One DESC,
  Two DESC NULLS FIRST;

The default for DESC ordering is NULLS FIRST so you could further simplify it to:

ORDER BY
  One DESC,
  Two DESC;

However, I would not take it this far as you are better explicitly stating that you are expecting NULL values to be ordered before non-NULL so future developers know that that is your intended ordering (rather than just an unintentional side-effect of your query).

thanks for clarification. do you know if that applies to denodo's VQL as well? — bobryash, Nov 06 '17 at 12:28
@MichaelBobryashov No idea as I have never used Denodo - if you need an answer to that then you can see if you can find it in their documentation or ask it as a new question with the appropriate tags (so that Denodo experts can answer it). — MT0, Nov 06 '17 at 12:36

score 1 · Accepted Answer · answered Nov 06 '17 at 11:20

1

Add the column two as third order condition

ORDER BY One DESC,
         CASE when Two = '' then 1 else 0 end DESC,
         Two DESC

The second order condition only puts empty entries first and not more.

answered Nov 06 '17 at 11:20

juergen d

201,996
37
293
362

thank you, it works! so, "else 0" part means just to ignore any other value? – bobryash Nov 06 '17 at 11:30
The `case` results in 2 values: 0 or 1. You sort by that. This means 1-values come first in the list and then all 0-values. – juergen d Nov 06 '17 at 11:43
1

@MichaelBobryashov The `CASE` expression, here, will always evaluate to `0` so this `ORDER BY` clause is the equivalent of `ORDER BY One DESC, Two DESC` and the [default is for `DESC` ordering to be `NULLS FIRST`](https://docs.oracle.com/database/121/SQLRF/statements_10002.htm#i2171079) (the `CASE` expression is useless). – MT0 Nov 06 '17 at 11:46
The explanation of why this works is entirely wrong! – MT0 Nov 06 '17 at 11:53

Random sorting with ORDER BY with CASE clause

2 Answers2