Re: How to Select Query by some pattern..

"deneb" <cygdeneb_at_yonsei.ac.kr> wrote in message news:1191809698.111511.258850_at_y42g2000hsy.googlegroups.com...
> 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?
>

SQL> with base_data as (

2 select day, seq, orderno,

3 lead(orderno) over (

4 order by seq

5 ) next_orderno,

6 row_number() over(order by seq) as rownumber

7 from cdos order by seq

8 )

9 select orderno

10 --, rownumber

11 , rownumber-nvl(lag(rownumber) over(order by rownumber),0)

12 from base_data

13 where orderno != nvl(next_orderno,' ')

14 ;

ORDERNO ROWNUMBER-NVL(LAG(ROWNUMBER)OV

• ------------------------------

LM001 2

LM003 2

LM005 1

LM001 1

LM002 3

LM001 2

LM004 2

7 rows selected Received on Mon Oct 08 2007 - 09:47:50 CDT