Create a grouping column

Create a Derived column
 Table Input
Level     Order
1            000001-01
2           000001.000001
2           000001.000002
1           000002-01
2           000002-01.000001-01
2          000002-01.000002-01
2          000002-01.000003-01

Desired Output
     Order                Grouping_Column (Derived Column)
     000001-01                       000001-01
     000001.000001                000001-01
     000001.000002                000001-01
     000002-01                       000002-01                
     000002-01.000001-01      000002-01
     000002-01.000002-01      000002-01
     000002-01.000003-01      000002-01
cookiejarAsked:
Who is Participating?
 
awking00Commented:
select order
,first_value(order) over (partition by substr(order,1,6) order by level) grouping_column
from input;

Note - level and order are keywords so column names have been modified in the example below -

SQL> select * from input;

      LEVL ORD
---------- --------------------
         1 000001-01
         2 000001.000001
         2 000001.000002
         1 000002-01
         2 000002-01.000001-01
         2 000002-01.000002-01
         2 000002-01.000003-01

SQL> select ord
  2  ,first_value(ord) over (partition b
g_column
  3  from input;

ORD                  GROUPING_COLUMN
-------------------- -------------------
000001-01            000001-01
000001.000001        000001-01
000001.000002        000001-01
000002-01            000002-01
000002-01.000001-01  000002-01
000002-01.000002-01  000002-01
000002-01.000003-01  000002-01
0
 
slightwv (䄆 Netminder) Commented:
What are the rules?

I'm not seeing them from what you posted.  How do you get the derived column?

Also provide your Oracle version.
0
 
DavidSenior Oracle Database AdministratorCommented:
What have you tried, or is this school work?
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
cookiejarAuthor Commented:
The derived column is what I would like to get from the input data.  

For example, the level  2s' rows should be  populated with 000001-01 in the derived column.
The 000002-01.000001-01 row derived column value should be   000002-01


1           000001-01
2           000001-01.000001-01
2           000001-01.000002-01
1           000002-01
2           000002-01.000001-01      

I just wanted to get an idea of how should I approach this. I'm  using ORACLE version 10.
0
 
slightwv (䄆 Netminder) Commented:
I still do not understand the question.

How do you get from input to derived?

I do not see how 000002-01.000001-01 becomes 000002-01.  I can assume you just take everything from the left of the period bu then I don't get how you get from 000001.000002 to 000001-01.

>>'m  using ORACLE version 10.

10gR1 or 10gR2?
0
 
slightwv (䄆 Netminder) Commented:
I'll give you the benefit of the doubt that this is not school work but I agree it does sound a little like it.


Since you never posted back with the requirements, I'll guess at them.

All derived columns must end with a '-01'.  Take the left side if a decimal exists.  Add the '-01' if it does not naturally exist.

See if this gives ou what you need:

select derived || case when instr(derived,'-') = 0 then '-01' end
from (
select regexp_substr(col1,'[0-9-]+') derived from tab1
);
0
 
awking00Commented:
Sorry, part of the query got cut off during cut and paste -
SQL> select ord
  2  ,first_value(ord) over (partition by substr(ord,1,6) order by levl) grouping_column
  3  from input;

ORD                  GROUPING_COLUMN
-------------------- --------------------
000001-01            000001-01
000001.000001        000001-01
000001.000002        000001-01
000002-01            000002-01
000002-01.000001-01  000002-01
000002-01.000002-01  000002-01
000002-01.000003-01  000002-01
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.