Home » SQL & PL/SQL » SQL & PL/SQL » grouping in ordinal way... (oracle 10.0.2.4 on windows 2003 server)
grouping in ordinal way... [message #440737] Tue, 26 January 2010 22:09 Go to next message
kang
Messages: 89
Registered: November 2007
Member
--case 1.
with tmp as(
select 8 seq, '10' div, '2008' yyyy from dual union all
select 7, '10', '2007' from dual union all
select 6, '10', '2006' from dual union all
select 5, '20', '2005' from dual union all
select 4, '20', '2004' from dual union all
select 3, '20', '2003' from dual union all
select 2, '10', '2002' from dual union all
select 1, '10', '2001' from dual
), real_data_in_db as(
select * from tmp order by seq
)
select * from real_data_in_db


the data in inserted in order of seq number
and I want to get recent yyyy which change to '10' of div value.

in this case I want to get '2006'
-- case 2
with tmp as(
select 8 seq, '10' div, '2008' yyyy from dual union all
select 7, '10', '2007' from dual union all
select 6, '10', '2006' from dual union all
select 5, '10', '2005' from dual union all
select 4, '10', '2004' from dual union all
select 3, '10', '2003' from dual union all
select 2, '10', '2002' from dual union all
select 1, '10', '2001' from dual
), real_data_in_db as(
select * from tmp order by seq
)
select * from real_data_in_db


if the case 2, I want to get '2001'

if I can group the div in ordinal way, I could get one.

thanks.
Re: grouping in ordinal way... [message #440784 is a reply to message #440737] Wed, 27 January 2010 00:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
the data in inserted in order of seq number
and I want to get recent yyyy which change to '10' of div value.

Not clear.
What is "recent yyyy"?

Quote:
in this case I want to get '2006'

Which case? The above one or the below one? And why '2006'? Why seq (as this the primary key, it seems).

Quote:
if the case 2, I want to get '2001'

Why?

Regards
Michel




Re: grouping in ordinal way... [message #440823 is a reply to message #440737] Wed, 27 January 2010 03:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This should point you in the right direction.
You need to use an analytic function (LAG in this case) to compare each row with the one before it to spot where DIV changes.

Have a play with this and see if you can work out how to get the results you need:
with tmp as(select 8 seq, '10' div, '2008' yyyy from dual union all
            select 7, '10', '2007' from dual union all
            select 6, '10', '2006' from dual union all
            select 5, '20', '2005' from dual union all
            select 4, '20', '2004' from dual union all
            select 3, '20', '2003' from dual union all
            select 2, '10', '2002' from dual union all
            select 1, '10', '2001' from dual)
    , real_data_in_db as(select * from tmp order by seq desc)
select seq
      ,div
      ,yyyy
      ,lag(div) over (order by seq) last_div
from real_data_in_db;
Re: grouping in ordinal way... [message #440835 is a reply to message #440737] Wed, 27 January 2010 05:45 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with 
  2    tmp as (
  3      select 8 seq, '10' div, '2008' yyyy from dual union all
  4      select 7, '10', '2007' from dual union all
  5      select 6, '10', '2006' from dual union all
  6      select 5, '20', '2005' from dual union all
  7      select 4, '20', '2004' from dual union all
  8      select 3, '20', '2003' from dual union all
  9      select 2, '10', '2002' from dual union all
 10      select 1, '10', '2001' from dual
 11    ),
 12    data as (
 13      select seq, div, yyyy,
 14             case
 15               when    div = 10 
 16                   and nvl(lead(div) over (order by seq desc),0) != 10
 17                 then yyyy
 18             end flag
 19      from tmp
 20    )
 21  select max(flag) yyyy from data
 22  /
YYYY
----
2006

1 row selected.

SQL> 
SQL> with 
  2    tmp as (
  3      select 8 seq, '10' div, '2008' yyyy from dual union all
  4      select 7, '10', '2007' from dual union all
  5      select 6, '10', '2006' from dual union all
  6      select 5, '10', '2005' from dual union all
  7      select 4, '10', '2004' from dual union all
  8      select 3, '10', '2003' from dual union all
  9      select 2, '10', '2002' from dual union all
 10      select 1, '10', '2001' from dual
 11    ),
 12    data as (
 13      select seq, div, yyyy,
 14             case
 15               when    div = 10 
 16                   and nvl(lead(div) over (order by seq desc),0) != 10
 17                 then yyyy
 18             end flag
 19      from tmp
 20    )
 21  select max(flag) yyyy from data
 22  /
YYYY
----
2001

1 row selected.

Regards
Michel
Previous Topic: Data from database to word doc
Next Topic: why DDL statements are autocommit
Goto Forum:
  


Current Time: Fri Dec 09 02:09:15 CST 2016

Total time taken to generate the page: 0.13695 seconds