CODEWITHSUNDEEP
sqloracle-database
kyooryu
kyooryu

Reputation: 1509

Oracle - DECODE - How will it sort when not every case is specified?

I have to use DECODE to implement custom sort:

SELECT col1, col2 FROM tbl ORDER BY DECODE(col1, 'a', 3, 'b', 2, 'c', 1) DESC

What will happen if col1 has more values that the three specified in decode clause?

Upvotes: 0

Views: 4017

Answers (3)

schurik
schurik

Reputation: 7928

DECODE will return NULL, for the values of col1 which are not specified.
The NULL-Values will be placed at the front per default .

if you want to change this behavior you can either define the default value in DECODE 

SELECT col1, col2 FROM tbl ORDER BY DECODE(col1, 'a', 3, 'b', 2, 'c', 1, 0) DESC

or NULLS LAST in the order clause

 SELECT col1, col2 FROM tbl ORDER BY DECODE(col1, 'a', 3, 'b', 2, 'c', 1) DESC NULLS LAST

Upvotes: 3

altsyset
altsyset

Reputation: 339

Normal it expects some result as a default if not NULL is all you get so add some value at the end like this

SELECT col1, col2 FROM tbl ORDER BY DECODE(col1, 'a', 3, 'b', 2, 'c', 1, 0) DESC

That way if col1 has more values they all will return 0

Upvotes: 1

Nicolas Durand
Nicolas Durand

Reputation: 280

the decode function will return NULL value and it is at the bottom of your sort. You can verify it: select decode('z','a', 3, 'b', 2, 'c', 1) from dual;

you can also control the appearance of the null value with NULLS LAST/NULLS FIRST in the order clause.

Upvotes: 1

Related Questions