Re: Q: something like rownum

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Thu, 17 Apr 2008 12:04:23 -0700 (PDT)
Message-ID: <f9f64820-171d-4cb4-a692-8ed5e7e2a718@t54g2000hsg.googlegroups.com>

On Apr 17, 2:26 pm, yf..._at_vtn1.victoria.tc.ca (Malcolm Dew-Jones) wrote:
> 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

A third option, do it with the SUM analytical function and DECODE: SELECT
  TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1) THE_DATE,   TO_CHAR(TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1),'DY') DAY FROM
  DUAL
CONNECT BY
  LEVEL<=20;

THE_DATE DAY

--------- ---
01-JAN-08 TUE
02-JAN-08 WED
03-JAN-08 THU
04-JAN-08 FRI

...
19-JAN-08 SAT
20-JAN-08 SUN Step 1:
SELECT
  SUM(DECODE(DAY,'SAT',0,'SUN',0,1)) OVER (ORDER BY THE_DATE) MY_COUNT,
  THE_DATE,
  DAY
FROM
  (SELECT
    TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1) THE_DATE,     TO_CHAR(TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1),'DY') DAY   FROM
    DUAL
  CONNECT BY
    LEVEL<=20);

  MY_COUNT THE_DATE DAY

---------- --------- ---
         1 01-JAN-08 TUE
         2 02-JAN-08 WED
         3 03-JAN-08 THU
         4 04-JAN-08 FRI
         4 05-JAN-08 SAT
         4 06-JAN-08 SUN
         5 07-JAN-08 MON
         6 08-JAN-08 TUE
         7 09-JAN-08 WED
         8 10-JAN-08 THU
         9 11-JAN-08 FRI
         9 12-JAN-08 SAT
         9 13-JAN-08 SUN
        10 14-JAN-08 MON
        11 15-JAN-08 TUE
        12 16-JAN-08 WED
        13 17-JAN-08 THU
        14 18-JAN-08 FRI
        14 19-JAN-08 SAT
        14 20-JAN-08 SUN

Now to remove the numbers that should not print, using a second DECODE:
SELECT
  DECODE(DAY,'SAT',NULL,'SUN',NULL,SUM(DECODE(DAY,'SAT',0,'SUN',0,1)) OVER (ORDER BY THE_DATE)) MY_COUNT,
  THE_DATE,
  DAY
FROM
  (SELECT
    TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1) THE_DATE,     TO_CHAR(TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1),'DY') DAY   FROM
    DUAL
  CONNECT BY
    LEVEL<=20);

MY 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
9  11-JAN-08 FRI
   12-JAN-08 SAT
   13-JAN-08 SUN
10 14-JAN-08 MON
11 15-JAN-08 TUE
12 16-JAN-08 WED
13 17-JAN-08 THU
14 18-JAN-08 FRI
   19-JAN-08 SAT
   20-JAN-08 SUN

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Thu Apr 17 2008 - 14:04:23 CDT