CODEWITHSUNDEEP
sqloracle-databasecase
bobryash
bobryash

Reputation: 149

Random sorting with ORDER BY with CASE clause

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: enter image description here enter image description here

How should I modify CASE clause to avoid it?

Upvotes: 0

Views: 411

Answers (2)

MT0
MT0

Reputation: 168106

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

Upvotes: 2

juergen d
juergen d

Reputation: 204794

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.

Upvotes: 1

Related Questions