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
|
