Re: Q: something like rownum

Malcolm Dew-Jones schrieb:
> Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
>
> This sounds like it should be super simple, but I can't think how to do
> it using plain SQL.
>
> I am going to display some rows, and I want to number them in the display,
> like using rownum, except that the number only goes up when the row has
> some property (I don't care if it displays or not when it doesn't go up).
>
>
> To explain, imagine
>
> select rownum , the_date , to_char(dates.the_date,'DY') DAY
> from my_table
> order by the_date
>
> shows
>
> 1 1-Jan-2008 TUE
> 2 2-Jan-2008 WED
> 3 3-Jan-2008 THU
> 4 4-Jan-2008 FRI
> 5 5-Jan-2008 SAT
> 6 6-Jan-2008 SUN
> 7 7-Jan-2008 MON
> 8 8-Jan-2008 TUE
>
> but I don't want to count the weekend. what I want to show would be the
> following instead
>
>
> 1 1-Jan-2008 TUE
> 2 2-Jan-2008 WED
> 3 3-Jan-2008 THU
> 4 4-Jan-2008 FRI
> 5-Jan-2008 SAT
> 6-Jan-2008 SUN
> 5 7-Jan-2008 MON
> 6 8-Jan-2008 TUE
>
> Looks simple, but how to do this in plain SQL?
>
> Feedback welcome, thanks.
>
> Malcolm

Alternatively you can achieve it with analytics (if you like to have nls independent code, slightly more code is required)

SQL> alter session set nls_territory='AMERICA';

Session altered.

SQL> alter session set nls_date_language='AMERICAN';

Session altered.

SQL>
SQL> with my_table as (

   2 select date '2008-01-01' + rownum -1 the_date    3 from dual connect by level <=10
   4 )
   5 select
   6 case
   7 when
   8 to_char(the_date,'d') not in (1,7)    9 then row_number()
  10 over(partition by case when to_char(the_date,'d') not in (1,7) then 1   11 end order by the_date)
  12 end row_num,
  13 the_date,
  14 to_char(the_date,'DY') DAY
  15 from my_table
  16 order by the_date;

    ROW_NUM THE_DATE DAY

---------- ------------------ ------------
          1 01-JAN-08          TUE
          2 02-JAN-08          WED
          3 03-JAN-08          THU
          4 04-JAN-08          FRI
            05-JAN-08          SAT
            06-JAN-08          SUN
          5 07-JAN-08          MON
          6 08-JAN-08          TUE
          7 09-JAN-08          WED
          8 10-JAN-08          THU

Best regards

Maxim Received on Thu Apr 17 2008 - 13:24:44 CDT