Home » SQL & PL/SQL » SQL & PL/SQL » SQL request without PL/SQL (Oracle 9i)
SQL request without PL/SQL [message #329478] Wed, 25 June 2008 09:22 Go to next message
Xaltotun_2008
Messages: 2
Registered: June 2008
Junior Member
Hi all. I'm trying to create an unusual SQL request. The problem look fairly simple. I have a table:
COLUMN1 COLUMN2
1 1
2 1
3 1
4 2
5 2
6 1
7 1
The order is set by COLUMN1. I need to find the minimum and maximum values in COLUMN1 in every continuous group of rows with the same value in COLUMN2, i.e. the desired result should look like
MIN MAX COLUMN2
1 3 1
4 5 2
6 7 1
If I use a simple "group by" statement the result will look like
MIN MAX COLUMN2
1 7 1
4 5 2
Is there a way to achieve the desired result by means of SQL without PL/SQL? It seems to me there must be a way but I cannot find it.
P.S. The tables look a bit jumbled, every column consists of one-digit numbers.
Re: SQL request without PL/SQL [message #329480 is a reply to message #329478] Wed, 25 June 2008 09:34 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guidelines as stated in URL above
Re: SQL request without PL/SQL [message #329485 is a reply to message #329478] Wed, 25 June 2008 09:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
In the words of Tom Kyte, Analytics Rock, Analytics Roll
create table grp_test (col_1 number, col_2 number);

insert into grp_test values (1, 1);
insert into grp_test values (2, 1);
insert into grp_test values (3, 1);
insert into grp_test values (4, 2);
insert into grp_test values (5, 2);
insert into grp_test values (6, 1);
insert into grp_test values (7, 1);

select col_2
      ,grp_start
      ,grp_end
from  (select col_1
             ,col_2
             ,case when col_2 != prev_col_2 then col_1 else null end grp_start
             ,nvl(lead(case when col_2 != next_col_2 then col_1 else null end,1,col_1) over (order by col_1)
                 ,case when col_2 != next_col_2 then col_1 else null end) grp_end

       from  (select col_1
                    ,col_2
                    ,lag(col_2,1,-1) over (order by col_1) prev_col_2
                    ,lead(col_2,1,-1) over (order by col_1) next_col_2
              from grp_test)
       where prev_col_2 != next_col_2)
where grp_start is not null;

     COL_2  GRP_START    GRP_END
---------- ---------- ----------
         1          1          3
         2          4          5
         1          6          7


If you want to understand how it works, run each of the nested sub queries by itself.

[Added correction to handle singleton values at the end of the list]
[Additional frig to deal with general case singelton rows]

[Updated on: Wed, 25 June 2008 10:08]

Report message to a moderator

Re: SQL request without PL/SQL [message #329500 is a reply to message #329478] Wed, 25 June 2008 10:47 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
This is almost copy & paste of tom kyte's version of grouping it.

  1  select col_2, min(col_1) grp_start, max(col_1) grp_end
  2  from
  3  (
  4  select col_1, col_2, max(grp_id) over(order by col_1) max_grp_id
  5  from
  6   (
  7    select col_1, col_2,
  8           case when
  9                    rownum = 1 or col_2 != lag(col_2) over(order by col_1)
 10                then rownum
 11           end grp_id
 12     from
 13     grp_test
 14    )
 15  )
 16* group by col_2, max_grp_id
SQL> /

     COL_2  GRP_START    GRP_END
---------- ---------- ----------
         1          1          3
         1          6          7
         2          4          5

@JRowbottom, Thanks for the insert script.

Regards

Raj
Re: SQL request without PL/SQL [message #329674 is a reply to message #329478] Thu, 26 June 2008 03:38 Go to previous message
Xaltotun_2008
Messages: 2
Registered: June 2008
Junior Member
Thanks for the solution. The problem is now solved.
Previous Topic: Date format error
Next Topic: regarding check constraints
Goto Forum:
  


Current Time: Sat Dec 10 08:45:02 CST 2016

Total time taken to generate the page: 0.09209 seconds