Home » SQL & PL/SQL » SQL & PL/SQL » Data Grouping (merged 3)
Data Grouping (merged 3) [message #408267] Mon, 15 June 2009 09:19 Go to next message
sarav1984
Messages: 4
Registered: June 2009
Location: India
Junior Member
Hi,

I have the data as mentioned below,

ALLOC_ID
========
ALLOC100
ALLOC101
ALLOC102
ALLOC103
ALLOC107
ALLOC108
ALLOC112
ALLOC115
ALLOC116
ALLOC117
ALLOC120
ALLOC121
ALLOC127

As we could see, the last 3 digits form a sort of sequence. So when the sequence breaks, a group must be formed. For the above input data the output must be like below,

ALLOC_ID GROUP_NUM
======== =========
ALLOC100 1
ALLOC101 1
ALLOC102 1
ALLOC103 1
ALLOC107 2
ALLOC108 3
ALLOC112 4
ALLOC115 5
ALLOC116 5
ALLOC117 5
ALLOC120 6
ALLOC121 6
ALLOC127 7

How to implement this using straight select statement without using PL/SQL blocks, no procedures, no functions?
Re: Data Grouping [message #408270 is a reply to message #408267] Mon, 15 June 2009 09:26 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>ALLOC107 2
>ALLOC108 3

What is logic behind ALLOC108 in Group 3 & not Group 2?
Re: Data Grouping [message #408271 is a reply to message #408267] Mon, 15 June 2009 09:27 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.


do NOT cross/multi-post
Data Grouping [message #408272 is a reply to message #408267] Mon, 15 June 2009 09:27 Go to previous messageGo to next message
sarav1984
Messages: 4
Registered: June 2009
Location: India
Junior Member
Hi,

I have the data as mentioned below,

ALLOC_ID
========
ALLOC100
ALLOC101
ALLOC102
ALLOC103
ALLOC107
ALLOC108
ALLOC112
ALLOC115
ALLOC116
ALLOC117
ALLOC120
ALLOC121
ALLOC127

As we could see, the last 3 digits form a sort of sequence. So when the sequence breaks, a group must be formed. For the above input data the output must be like below,

ALLOC_ID GROUP_NUM
======== =========
ALLOC100 1
ALLOC101 1
ALLOC102 1
ALLOC103 1
ALLOC107 2
ALLOC108 3
ALLOC112 4
ALLOC115 5
ALLOC116 5
ALLOC117 5
ALLOC120 6
ALLOC121 6
ALLOC127 7

How to implement this using straight select statement without using PL/SQL blocks, no procedures, no functions?
Re: Data Grouping [message #408273 is a reply to message #408272] Mon, 15 June 2009 09:29 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Stop spaming this forum!
Re: Data Grouping [message #408275 is a reply to message #408270] Mon, 15 June 2009 09:33 Go to previous messageGo to next message
sarav1984
Messages: 4
Registered: June 2009
Location: India
Junior Member
Hi,

Sorry for that typo. Thanks for pointing out.
ALLOC108 must be in GROUP_NUM 2.

I am putting the upadted expected output.

ALLOC_ID GROUP_NUM
======== =========
ALLOC100 1
ALLOC101 1
ALLOC102 1
ALLOC103 1
ALLOC107 2
ALLOC108 2
ALLOC112 3
ALLOC115 4
ALLOC116 4
ALLOC117 4
ALLOC120 5
ALLOC121 5
ALLOC127 6

Thanks,
Saravanan
Re: Data Grouping [message #408276 is a reply to message #408267] Mon, 15 June 2009 09:41 Go to previous messageGo to next message
l0b0
Messages: 13
Registered: November 2006
Junior Member
You could try the lag function to get the difference between this and the previous row. Then you can use a decode and another lag to get the group number as either last group or last group + 1. HTH.
Re: Data Grouping (merged 3) [message #408277 is a reply to message #408267] Mon, 15 June 2009 09:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Data Grouping (merged 3) [message #408281 is a reply to message #408277] Mon, 15 June 2009 10:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
One way to do it (that I think @Barbara introduced me to) requires nothing more than Rownum (or Row_number over ... if you're that way inclined).

If you look at the results of this:
with src as (select 'ALLOC100' col_1 from dual union all
select 'ALLOC101' from dual union all
select 'ALLOC102' from dual union all
select 'ALLOC103' from dual union all
select 'ALLOC107' from dual union all
select 'ALLOC108' from dual union all
select 'ALLOC112' from dual union all
select 'ALLOC115' from dual union all
select 'ALLOC116' from dual union all
select 'ALLOC117' from dual union all
select 'ALLOC120' from dual union all
select 'ALLOC121' from dual union all
select 'ALLOC127' from dual)
select col_1
      ,to_number(substr(col_1,6)) - row_number() over (order by col_1) rnum
from src       
you see that the numeric values group themselves.

All you have to do is to perform a dense_rank on these values, and your groups just fall out:
select col_1
      ,num_1
      ,dense_rank() over (order by num_1 +1  - rnum) grp
from  (select col_1
             ,substr(col_1,6) num_1
             ,row_number() over (order by col_1) rnum
       from src);
Re: Data Grouping (merged 3) [message #408290 is a reply to message #408281] Mon, 15 June 2009 11:07 Go to previous message
sarav1984
Messages: 4
Registered: June 2009
Location: India
Junior Member
Hi JRowbottom,

Thanks a lot for the sample. That works.
Could you please clarify me the significance of +1 in dense_rank() over (order by num_1 +1 - rnum)? without the +1 the results were same. I just wanted to understand.

Thanks,
Saravanan
Previous Topic: move TABLESPACE
Next Topic: Dynamic crosstab script
Goto Forum:
  


Current Time: Sat Dec 03 21:59:08 CST 2016

Total time taken to generate the page: 0.09645 seconds