Re: Q: something like rownum

Charles Hooper (hooperc2000_at_yahoo.com) wrote:
: On Apr 17, 2:26=A0pm, 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
: >
: > =A0 =A0 =A0 =A0 select rownum =A0, the_date , to_char(dates.the_date,'DY')=
: DAY
: > =A0 =A0 =A0 =A0 from my_table
: > =A0 =A0 =A0 =A0 order by the_date
: >
: > shows
: >
: > =A0 =A0 =A0 =A0 1 =A0 =A0 =A0 1-Jan-2008 =A0 =A0 =A0TUE
: > =A0 =A0 =A0 =A0 2 =A0 =A0 =A0 2-Jan-2008 =A0 =A0 =A0WED
: > =A0 =A0 =A0 =A0 3 =A0 =A0 =A0 3-Jan-2008 =A0 =A0 =A0THU
: > =A0 =A0 =A0 =A0 4 =A0 =A0 =A0 4-Jan-2008 =A0 =A0 =A0FRI
: > =A0 =A0 =A0 =A0 5 =A0 =A0 =A0 5-Jan-2008 =A0 =A0 =A0SAT
: > =A0 =A0 =A0 =A0 6 =A0 =A0 =A0 6-Jan-2008 =A0 =A0 =A0SUN
: > =A0 =A0 =A0 =A0 7 =A0 =A0 =A0 7-Jan-2008 =A0 =A0 =A0MON
: > =A0 =A0 =A0 =A0 8 =A0 =A0 =A0 8-Jan-2008 =A0 =A0 =A0TUE
: >
: > but I don't want to count the weekend. =A0what I want to show would be the=

: > following instead
: >
: > =A0 =A0 =A0 =A0 1 =A0 =A0 =A0 1-Jan-2008 =A0 =A0 =A0TUE
: > =A0 =A0 =A0 =A0 2 =A0 =A0 =A0 2-Jan-2008 =A0 =A0 =A0WED
: > =A0 =A0 =A0 =A0 3 =A0 =A0 =A0 3-Jan-2008 =A0 =A0 =A0THU
: > =A0 =A0 =A0 =A0 4 =A0 =A0 =A0 4-Jan-2008 =A0 =A0 =A0FRI
: > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 5-Jan-2008 =A0 =A0 =A0SAT
: > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 6-Jan-2008 =A0 =A0 =A0SUN
: > =A0 =A0 =A0 =A0 5 =A0 =A0 =A0 7-Jan-2008 =A0 =A0 =A0MON
: > =A0 =A0 =A0 =A0 6 =A0 =A0 =A0 8-Jan-2008 =A0 =A0 =A0TUE
: >
: > Looks simple, but how to do this in plain SQL? =A0
: >
: > 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<=3D20;

: THE_DATE DAY
: --------- ---
: 01-JAN-08 TUE
: 02-JAN-08 WED
: 03-JAN-08 THU
: 04-JAN-08 FRI
: =2E..
: 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<=3D20);

: 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<=3D20);

: 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.

Thanks all, a few good ideas here, thanks. Received on Thu Apr 17 2008 - 15:16:34 CDT