Home » SQL & PL/SQL » SQL & PL/SQL » Help requried in Writing query with Analytical function (Oracle, 10g, Linux)
Help requried in Writing query with Analytical function [message #349147] |
Fri, 19 September 2008 02:07  |
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   |
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 #349179 is a reply to message #349172] |
Fri, 19 September 2008 03:49   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
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   |
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
|
|
|
|
Goto Forum:
Current Time: Fri Feb 07 14:59:15 CST 2025
|