Re: Q: something like rownum
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