Re: How can I know how many same orderno is shown in rows sequentially?

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Mon, 08 Oct 2007 08:57:32 +0200
Message-ID: <4709D4DC.1050308@gmail.com>

deneb schrieb:
> hi there,
>
> I have a table that has below columns and rows.
>
>
> DAY SEQ ORDERNO
> -----------------------------------------------------------------
> 20071002 1 LM001
> 20071002 2 LM001
> 20071002 3 LM003
> 20071002 4 LM003
> 20071002 5 LM005
> 20071002 6 LM001
> 20071002 7 LM002
> 20071002 8 LM002
> 20071002 9 LM002
> 20071002 10 LM001
> 20071002 11 LM001
> 20071002 12 LM004
> 20071002 13 LM004
>
>
> How can I get a datas like this ?
>
>
> ORDERNO COUNT
> --------------------------------------------
> LM001 2
> LM003 2
> LM005 1
> LM001 1
> LM002 3
> LM001 2
> LM004 2
>
>
> That is, how can I know how many same orderno is shown in rows
> sequentially?
> I'm using oracle 9i. PL-SQL statements could be used.
>

SQL> with t as (

  2  select '20071002' day,1 seq,'LM001' orderno from dual union all
  3  select '20071002',2 ,'LM001' from dual union all
  4  select '20071002',3 ,'LM003' from dual union all
  5  select '20071002',4 ,'LM003' from dual union all
  6  select '20071002',5 ,'LM005' from dual union all
  7  select '20071002',6 ,'LM001' from dual union all
  8  select '20071002',7 ,'LM002' from dual union all
  9  select '20071002',8 ,'LM002' from dual union all
 10  select '20071002',9 ,'LM002' from dual union all
 11  select '20071002',10,'LM001' from dual union all
 12  select '20071002',11,'LM001' from dual union all
 13  select '20071002',12,'LM004' from dual union all
 14  select '20071002',13,'LM004' from dual

 15 )
 16 -- end test data
 17 select orderno, count(group_no) cnt  18 from (select day,

 19                 seq,
 20                 orderno,
 21                 sum(group_start) over(order by day, seq) group_no
 22            from (select day,
 23                         seq,
 24                         orderno,
 25                         decode(orderno,
 26                                lag(orderno) over(order by day, seq),
 27                                0,
 28                                1) group_start
 29                    from t))

 30 group by orderno, group_no
 31 order by group_no
 32 /

ORDER CNT
----- ----------

LM001          2
LM003          2
LM005          1
LM001          1
LM002          3
LM001          2
LM004          2

7 rows selected.

Best regards

Maxim Received on Mon Oct 08 2007 - 01:57:32 CDT