Home » SQL & PL/SQL » SQL & PL/SQL » Grouping via analytical function (ORA 10g)
Grouping via analytical function [message #415531] Tue, 28 July 2009 03:29 Go to next message
ph1975
Messages: 7
Registered: February 2008
Location: Zürich
Junior Member
Dear all,

i am looking for a analytical function for the following problem:

create table z (
    groupid varchar2(10)
    , group_name varchar2(10)
    , runid number
);


insert into z (1,'A',1);
insert into z (1,'A',2);
insert into z (1,'A',3);
insert into z (1,'B',4);
insert into z (1,'B',5);
insert into z (1,'A',6);
insert into z (1,'A',7);
insert into z (1,'C',8);

insert into z (2,'A',1);
insert into z (2,'A',2);
insert into z (2,'A',3);
insert into z (2,'B',4);
insert into z (2,'B',5);
insert into z (2,'C',6);
commit;


Required Result
     
1 A 1  1
1 A 2  1
1 A 3  1
1 B 4  2
1 B 5  2
1 A 6  3
1 A 7  3
1 C 8  4

2 A 1  1
2 A 2  1
2 A 3  1
2 B 4  2
2 B 5  2
2 C 6  3
       ^ analytical function ?


I tried rank(), dense_rank() but did not succeed.
Thanks very much for your help in advance,
Regards,
Philipp
Re: Grouping via analytical function [message #415534 is a reply to message #415531] Tue, 28 July 2009 03:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I tried rank(), dense_rank() but did not succeed.

Post what you tried but DENSE_RANK should work.

Regards
Michel

[Updated on: Tue, 28 July 2009 03:32]

Report message to a moderator

Re: Grouping via analytical function [message #415538 is a reply to message #415531] Tue, 28 July 2009 03:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I didn't see some details in your result.

You must explain in DETAILS with WORDS what should be the result.

Regards
Michel
Re: Grouping via analytical function [message #415551 is a reply to message #415531] Tue, 28 July 2009 04:03 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Quote:
insert into z (1,'A',1);


doesn't follow Oracle INSERT syntax.

regards,
Delna
Re: Grouping via analytical function [message #415553 is a reply to message #415531] Tue, 28 July 2009 04:08 Go to previous messageGo to next message
ph1975
Messages: 7
Registered: February 2008
Location: Zürich
Junior Member
ok, sorry for leaving this part out.

What i wanted to achieve is, to get the same identifier per group.

Example:

ID Group Run-Id Expected Result
--------------------------------------
1 A 1 1
1 A 2 1
1 Z 3 2
1 Z 4 2
1 Z 5 2
1 A 6 3
1 A 7 3 <- must not be "1" again
1 C 8 4
1 C 9 4
--------------------------------------


Thanks a lot,
Philipp
Re: Grouping via analytical function [message #415557 is a reply to message #415553] Tue, 28 July 2009 04:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> break on groupid skip 1 dup
SQL> with 
  2    data as (
  3      select groupid, group_name, runid, 
  4             case 
  5               when nvl(lag(group_name) over (partition by groupid order by runid),0) != group_name
  6                 then row_number() over(order by groupid, runid)
  7             end flag
  8      from z
  9    ),
 10    flagged as (
 11      select groupid, group_name, runid, 
 12             last_value(flag ignore nulls) over(order by groupid, runid) flag
 13      from data d
 14    )
 15  select groupid, group_name, runid, 
 16         dense_rank() over(partition by groupid order by flag) res
 17  from flagged
 18  order by 1,3
 19  /
GROUPID    GROUP_NAME      RUNID        RES
---------- ---------- ---------- ----------
1          A                   1          1
1          A                   2          1
1          A                   3          1
1          B                   4          2
1          B                   5          2
1          A                   6          3
1          A                   7          3
1          C                   8          4

2          A                   1          1
2          A                   2          1
2          A                   3          1
2          B                   4          2
2          B                   5          2
2          C                   6          3

Regards
Michel

Re: Grouping via analytical function [message #415559 is a reply to message #415531] Tue, 28 July 2009 04:22 Go to previous messageGo to next message
ph1975
Messages: 7
Registered: February 2008
Location: Zürich
Junior Member
the correct statements:


Insert into Z
(GROUPID, GROUP_NAME, RUNID)
Values
('1', 'A', 1);
Insert into Z
(GROUPID, GROUP_NAME, RUNID)
Values
('1', 'A', 2);
Insert into Z
(GROUPID, GROUP_NAME, RUNID)
Values
('1', 'A', 3);
Insert into Z
(GROUPID, GROUP_NAME, RUNID)
Values
('1', 'C', 4);
Insert into Z
(GROUPID, GROUP_NAME, RUNID)
Values
('1', 'C', 5);
Insert into Z
(GROUPID, GROUP_NAME, RUNID)
Values
('1', 'B', 8);
Insert into Z
(GROUPID, GROUP_NAME, RUNID)
Values
('1', 'A', 7);
Insert into Z
(GROUPID, GROUP_NAME, RUNID)
Values
('1', 'A', 6);
COMMIT;
Re: Grouping via analytical function [message #415619 is a reply to message #415531] Tue, 28 July 2009 07:02 Go to previous message
ph1975
Messages: 7
Registered: February 2008
Location: Zürich
Junior Member
Thanks Michel,

your solution worked fine!

Cheers,
Philipp
Previous Topic: Tunning of pl/sql package (merged 6)
Next Topic: Convert function, problem with fonts
Goto Forum:
  


Current Time: Fri Dec 02 16:40:09 CST 2016

Total time taken to generate the page: 0.20241 seconds