Home » SQL & PL/SQL » SQL & PL/SQL » numbering partition numbers (oracle 10g on windows 2003 server)
numbering partition numbers [message #422865] Sun, 20 September 2009 23:23 Go to next message
kang
Messages: 89
Registered: November 2007
Member

with data as(
  select 'a' col1, '1' col2 from dual union
  select 'a' col1, '2' col2 from dual union
  select 'b' col1, '3' col2 from dual union
  select 'b' col1, '4' col2 from dual union
  select 'c' col1, '5' col2 from dual union
  select 'd' col1, '6' col2 from dual
)
select col1,col2,
       row_number() over(partition by col1 order by col2) rn
  from data

col1,col2,rn,partition_no(desired column)
-------------------------
a 1 1 ->1
a 2 2 ->1
b 3 1 ->2
b 4 2 ->2
c 5 1 ->3
d 6 1 ->4

how to number the partition number like the above?
Re: numbering partition numbers [message #422867 is a reply to message #422865] Sun, 20 September 2009 23:40 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at DENSE_RANK function... or use ascii(col1)-ascii('a')+1 Wink .

Regards
Michel

[Updated on: Sun, 20 September 2009 23:42]

Report message to a moderator

Re: numbering partition numbers [message #422879 is a reply to message #422867] Mon, 21 September 2009 01:05 Go to previous messageGo to next message
kang
Messages: 89
Registered: November 2007
Member
with data as(
  select 'a' col1, '1' col2 from dual union
  select 'a' col1, '2' col2 from dual union
  select 'b' col1, '3' col2 from dual union
  select 'b' col1, '4' col2 from dual union
  select 'c' col1, '5' col2 from dual union
  select 'd' col1, '6' col2 from dual
)
select col1,col2,
       dense_rank() over(order by col1)
  from data


this works.

sorry. there is lack of sources
any hints on dense_rank within group ...?

thanks.
Re: numbering partition numbers [message #422884 is a reply to message #422879] Mon, 21 September 2009 01:28 Go to previous message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Same link?

Regards
Michel
Previous Topic: ORA-02291: integrity constraint
Next Topic: Explain Plan (merged 2)
Goto Forum:
  


Current Time: Tue Sep 27 12:42:22 CDT 2016

Total time taken to generate the page: 0.12534 seconds