Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: How can I know how many same orderno is shown in rows sequentially?
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 dual15 )
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))
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
![]() |
![]() |