Home » SQL & PL/SQL » SQL & PL/SQL » Help requried in Writing query with Analytical function (Oracle, 10g, Linux)
icon7.gif  Help requried in Writing query with Analytical function [message #349147] Fri, 19 September 2008 02:07 Go to next message
shankhan
Messages: 28
Registered: September 2008
Location: Pakistan
Junior Member
Hi,
I need to write a query on below given data.

Col 1	Col 2	Required Output
1        1      2
2               2
3        1      3
4        1      3
5               3


What I want is to get the count of rows uptil the "Col 2" field is NULL. But when the "Col 2" field is NULL in the previous row it should start a new counter.
The required output column is also given above and the script to create table and reference data is given below.



create table temp_tab ( col1 varchar2(10), col2 varchar2(10));

INSERT INTO temp_tab
            (col1, col2
            )
     VALUES ('1', '1'
            );
INSERT INTO temp_tab
            (col1
            )
     VALUES ('2'
            );
INSERT INTO temp_tab
            (col1, col2
            )
     VALUES ('3', '1'
            );
INSERT INTO temp_tab
            (col1, col2
            )
     VALUES ('4', '1'
            );
INSERT INTO temp_tab
            (col1
            )
     VALUES ('5'
            );[CODE][/CODE]

[Updated on: Fri, 19 September 2008 02:33]

Report message to a moderator

Re: Help requried in Writing query with Analytical function [message #349161 is a reply to message #349147] Fri, 19 September 2008 02:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Thankyou for providing a test case, and sparing us from the (seemingly mandatory) exhortations to follow the guidelines.

Well, that was a nice little warmup to get the neurons firing.

The trick with problems like this is to create a column that you can use to group the rows you want to count together with.

In this case, we need to create a column that will put the first two rows into one group, and the next three rows into a second group.

A way to do this is:
select col1
      ,col2
      ,sum(nvl2(col2,0,1)) over (order by col1 desc) grp 
from   temp_tab

This simply counts the number of Nulls between the current row and the end of the data, providing us with our groupings:
COL1       COL2              GRP
---------- ---------- ----------
5                              1
4          1                   1
3          1                   1
2                              2
1          1                   2


From here it now just a case of counting the number of members in each group:
select col1
      ,col2
      ,count(*) over (partition by grp)
from (select col1
            ,col2
            ,sum(nvl2(col2,0,1)) over (order by col1 desc) grp 
      from temp_tab)
order by col1;

COL1       COL2       COUNT(*)OVER(PARTITIONBYGRP)
---------- ---------- ----------------------------
1          1                                     2
2                                                2
3          1                                     3
4          1                                     3
5                                                3


Re: Help requried in Writing query with Analytical function [message #349172 is a reply to message #349161] Fri, 19 September 2008 03:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Gosh! JRowbottom beat me to post this solution => cancel mine
I will try to find another one.

Regards
Michel
Re: Help requried in Writing query with Analytical function [message #349178 is a reply to message #349161] Fri, 19 September 2008 03:36 Go to previous messageGo to next message
shankhan
Messages: 28
Registered: September 2008
Location: Pakistan
Junior Member
You guys are totally brilliant.
Thanks a lot.
Re: Help requried in Writing query with Analytical function [message #349179 is a reply to message #349172] Fri, 19 September 2008 03:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here are 2 other ways.
They just convert question into SQL and starts a new group each time the previous col2 is null.
SQL> with
  2    data as (
  3      select col1, col2,
  4             case when lag(col2) over (order by col1) is null
  5                  then row_number() over (order by col1)
  6             end grp
  7      from temp_tab
  8    ),
  9    grouped as (
 10      select col1, col2,
 11             max(grp) over (order by col1) grp
 12      from data
 13    )
 14  select col1, col2, 
 15         count(*) over (partition by grp) cnt
 16  from grouped
 17  order by col1
 18  /
COL1       COL2              CNT
---------- ---------- ----------
1          1                   2
2                              2
3          1                   3
4          1                   3
5                              3

5 rows selected.

SQL> 
SQL> with 
  2    data as (
  3      select col1, col2, 
  4             lag(col1) over (order by col1) prev_col1,
  5             lag(col2) over (order by col1) prev_col2
  6      from temp_tab
  7      where rownum > 0
  8    ),
  9    grouped as (
 10      select col1, col2, connect_by_root col1 grp
 11      from data
 12      connect by     prior col1 = prev_col1
 13                 and prev_col2 is not null
 14      start with prev_col2 is null
 15    )
 16  select col1, col2,
 17         count(*) over (partition by grp) cnt
 18  from grouped
 19  order by col1
 20  /
COL1       COL2              CNT
---------- ---------- ----------
1          1                   2
2                              2
3          1                   3
4          1                   3
5                              3

5 rows selected.

(prev_col1 is not necessary with the current example, it is just there to support the case where col1 are not consecutive.)

Regards
Michel

[Updated on: Fri, 19 September 2008 03:51]

Report message to a moderator

Re: Help requried in Writing query with Analytical function [message #349191 is a reply to message #349179] Fri, 19 September 2008 04:21 Go to previous messageGo to next message
shankhan
Messages: 28
Registered: September 2008
Location: Pakistan
Junior Member
Brilliant Guys!
Now if add i another three below given rows in this table.

Insert into TEMP_TAB
   (COL1, COL2)
 Values
   (6, 1);
Insert into TEMP_TAB
   (COL1, COL2)
 Values
   (7, 1);
Insert into TEMP_TAB
   (COL1)
 Values
   (8);


The requried output will be something like this.

COL1	COL2	Count	GRP
1	1	2	3
2		2	3
3	1	3	2
4	1	3	2
5		3	2
6	1	3	1
7	1	3	1
8		3	1


Now out of these rows i want to pick those rows that have max value for col2 based on group grp. Since GRP 1 & 2 both have value 3, I want to pick any of GRP 1 OR 2.

So, the output will be something like this:

COL1	COL2	Count	GRP
3	1	3	2
4	1	3	2
5		3	2


OR LIKE THIS

COL1	COL2	Count	GRP
6	1	3	1
7	1	3	1
8		3	1
Re: Help requried in Writing query with Analytical function [message #349192 is a reply to message #349191] Fri, 19 September 2008 04:28 Go to previous message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just use the previous queries as inline view (adding the group number) then it is easy to get the max count and select one group with this one.

Regards
Michel
Previous Topic: Avoid Mutating Trigger error
Next Topic: Job Scheduling
Goto Forum:
  


Current Time: Wed Dec 07 04:58:56 CST 2016

Total time taken to generate the page: 0.12140 seconds