ORDER BY 'constant' oracle partition [message #624717] |
Thu, 25 September 2014 00:08 |
|
kiwinz
Messages: 13 Registered: September 2014 Location: WELLINGTON
|
Junior Member |
|
|
Hi
I am looking to know the difference between, order by 1 [ which refers first column ] in select statement and order by 1,2 in partition by
example below
row_number() over (partition by T1.COL1 order by T1.COL1
,T1.NUM1 desc nulls last
,T1.COL2 desc nulls first
,T1.COL3 desc nulls first
,T1.DATE_TIME desc nulls last
,case when substr(T1.INDICATOR,1,1) = 'P' then 1
when substr(T1.INDICATOR,1,1) = 'M' then 3
else 2
end desc)
Kindly suggest regarding the above
Thanks
|
|
|
|
|
|
|
|
|
Re: ORDER BY 'constant' oracle partition [message #624744 is a reply to message #624732] |
Thu, 25 September 2014 02:31 |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
All you are doing is dynamically assigning a numerical value to a string based on the custom sort that you want as a standard ascii sort won't give you the order required:
with t as (select 'p' ind, 1 id from dual union all
select 'a' ind, 2 id from dual union all
select 'z' ind, 3 id from dual )
select *
from t
order by case ind
when 'p' then 3
when 'a' then 2
when 'z' then 1
end;
IND ID
z 3
a 2
p 1
|
|
|
|