Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Girish.

Asked: January 02, 2006 - 11:39 pm UTC

Last updated: January 04, 2006 - 8:58 am UTC

Version: 9

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I would like to have a query where in ORDER BY caluse should have a DECODE function as shown below ,But I feel its not working

select deptno,ename
from emp
order by decode(deptno,10,'ename',20,'job',30,'hiredate')||' '||decode(deptno,10,'desc',20,'asc',30,'desc');

It should order by as follows

select deptno,ename
from emp
order by ename desc; -- if dept no is 10

Is my decode correct ? does it serve the purpose or will it be ignored

Result shows that it is not decode considered

DEPTNO ENAME
---------- ----------
10 CLARK
10 KING
10 MILLER
30 ALLEN
30 WARD
30 MARTIN
30 TURNER
30 JAMES
30 BLAKE
20 SMITH
20 SCOTT

DEPTNO ENAME
---------- ----------
20 FORD
20 ADAMS
20 JONES

Please clarify my doubt

Regds
Girish





and Tom said...

you are ordering by the character string literal 'ename' or 'job' or 'hiredate'

If the goal is:

for deptno 10, order by ename desc
deptno 20, order by job asc
deptno 30, order by hiredate desc

you would

order by decode( deptno, 10, ename ) desc,
decode( deptno, 20, job ) asc,
decode( deptno, 30, hiredate ) desc;




Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Final order of results

Narendra, January 04, 2006 - 2:27 am UTC

Tom,

Just curious to know how this query will work. My understanding is ORDER BY is applied on entire resultset of a query. If EMP has one row each for, say DEPTNO 10, 20 and 30, does it make any sense to order the results?
I am not actually able to understand actual requirement behind this query (unless it is for a single DEPTNO).

Thanks

Tom Kyte
January 04, 2006 - 8:58 am UTC

they want to order by different fields for each deptno - that is all.

Chuck Jolley, January 05, 2006 - 11:43 am UTC

After some testing what was interesting to me was that Oracle seemed to throw in a leading "order by dept_no asc" that wasn't asked for explicitly.

With some more testing it becomes clearer that this is a side effect of the nulls created by the decode statments and that an explicit "order by 1 asc" leading the sort would ensure the most commonly expected result.

eg
-- no sorting
chajol@tax.experior>select *
2 from (select substr(acct_no, 2, 1) acct_grp,
3 tax_dist,
4 original_amount,
5 to_char(created_date, 'hh24:mi:ss') created_date
6 from accounts
7 where tax_year = '2005'
8 and tax_type = 'PUBLIC UTILITY'
9 and substr(acct_no, 2, 1) in ('G', 'E', 'T')
10 and fire_dist is not null);

A TAX_DIST ORIGINAL_AMOUNT CREATED_
- -------------------- --------------- --------
G 103 783.13 10:27:41
E 103 2095.57 10:27:41
T 103 1149.23 10:27:48
T 103 791.86 10:27:48
T 103 7979.17 10:27:49
T 107 13359.15 10:30:36
G 107 1254.78 10:30:37
T 109 654.72 10:40:43
T 109 8002.01 10:40:43
E 109 174.94 10:53:55
T 322 2224.71 10:54:03
T 322 292.96 10:54:04
G 322 581.27 10:54:04
E 322 522.46 11:00:26
T 106 74501.81 11:00:28
G 106 33588.19 11:00:30
E 106 114150.32 11:06:32
T 012 7127.22 11:06:37

18 rows selected.

--sort 1
chajol@tax.experior>select *
2 from (select substr(acct_no, 2, 1) acct_grp,
3 tax_dist,
4 original_amount,
5 to_char(created_date, 'hh24:mi:ss') created_date
6 from accounts
7 where tax_year = '2005'
8 and tax_type = 'PUBLIC UTILITY'
9 and substr(acct_no, 2, 1) in ('G', 'E', 'T')
10 and fire_dist is not null)
11 order by decode(acct_grp, 'E', tax_dist) asc,
12 decode(acct_grp, 'G', created_date) asc,
13 decode(acct_grp, 'T', original_amount) asc;

A TAX_DIST ORIGINAL_AMOUNT CREATED_
- -------------------- --------------- --------
E 103 2095.57 10:27:41
E 106 114150.32 11:06:32
E 109 174.94 10:53:55
E 322 522.46 11:00:26
G 103 783.13 10:27:41
G 107 1254.78 10:30:37
G 322 581.27 10:54:04
G 106 33588.19 11:00:30
T 322 292.96 10:54:04
T 109 654.72 10:40:43
T 103 791.86 10:27:48
T 103 1149.23 10:27:48
T 322 2224.71 10:54:03
T 012 7127.22 11:06:37
T 103 7979.17 10:27:49
T 109 8002.01 10:40:43
T 107 13359.15 10:30:36
T 106 74501.81 11:00:28

18 rows selected.

--swap the last two decodes in the order by clause
chajol@tax.experior>select *
2 from (select substr(acct_no, 2, 1) acct_grp,
3 tax_dist,
4 original_amount,
5 to_char(created_date, 'hh24:mi:ss') created_date
6 from accounts
7 where tax_year = '2005'
8 and tax_type = 'PUBLIC UTILITY'
9 and substr(acct_no, 2, 1) in ('G', 'E', 'T')
10 and fire_dist is not null)
11 order by decode(acct_grp, 'E', tax_dist) asc,
12 decode(acct_grp, 'T', original_amount) asc,
13 decode(acct_grp, 'G', created_date) asc
14 ;

A TAX_DIST ORIGINAL_AMOUNT CREATED_
- -------------------- --------------- --------
E 103 2095.57 10:27:41
E 106 114150.32 11:06:32
E 109 174.94 10:53:55
E 322 522.46 11:00:26
T 322 292.96 10:54:04
T 109 654.72 10:40:43
T 103 791.86 10:27:48
T 103 1149.23 10:27:48
T 322 2224.71 10:54:03
T 012 7127.22 11:06:37
T 103 7979.17 10:27:49
T 109 8002.01 10:40:43
T 107 13359.15 10:30:36
T 106 74501.81 11:00:28
G 103 783.13 10:27:41
G 107 1254.78 10:30:37
G 322 581.27 10:54:04
G 106 33588.19 11:00:30

18 rows selected.

chajol@tax.experior>



more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.