dbTalk Databases Forums  

Q: something like rownum

Go Back dbTalk Databases Forums Databases comp.databases.oracle.misc Q: something like rownum

comp.databases.oracle.misc comp.databases.oracle.misc


Discuss Q: something like rownum in the comp.databases.oracle.misc forum.



Reply
Page 2 of 2 < 1 2
 
Thread Tools Display Modes
  #11  
Old   
Charles Hooper
 
Posts: n/a

Default Re: Q: something like rownum - 04-17-2008 , 02:04 PM





On Apr 17, 2:26*pm, yf... (AT) vtn1 (DOT) victoria.tc.ca (Malcolm Dew-Jones)
wrote:
Quote:
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,'S AT',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.


Reply With Quote
  #12  
Old   
Charles Hooper
 
Posts: n/a

Default Re: Q: something like rownum - 04-17-2008 , 02:04 PM





On Apr 17, 2:26*pm, yf... (AT) vtn1 (DOT) victoria.tc.ca (Malcolm Dew-Jones)
wrote:
Quote:
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,'S AT',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.


Reply With Quote
  #13  
Old   
Charles Hooper
 
Posts: n/a

Default Re: Q: something like rownum - 04-17-2008 , 02:04 PM


On Apr 17, 2:26*pm, yf... (AT) vtn1 (DOT) victoria.tc.ca (Malcolm Dew-Jones)
wrote:
Quote:
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,'S AT',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.


Reply With Quote
  #14  
Old   
Malcolm Dew-Jones
 
Posts: n/a

Default Re: Q: something like rownum - 04-17-2008 , 03:16 PM


Charles Hooper (hooperc2000 (AT) yahoo (DOT) com) wrote:
: On Apr 17, 2:26=A0pm, yf... (AT) vtn1 (DOT) 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,'S AT',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.

Reply With Quote
  #15  
Old   
Malcolm Dew-Jones
 
Posts: n/a

Default Re: Q: something like rownum - 04-17-2008 , 03:16 PM


Charles Hooper (hooperc2000 (AT) yahoo (DOT) com) wrote:
: On Apr 17, 2:26=A0pm, yf... (AT) vtn1 (DOT) 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,'S AT',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.

Reply With Quote
  #16  
Old   
Malcolm Dew-Jones
 
Posts: n/a

Default Re: Q: something like rownum - 04-17-2008 , 03:16 PM


Charles Hooper (hooperc2000 (AT) yahoo (DOT) com) wrote:
: On Apr 17, 2:26=A0pm, yf... (AT) vtn1 (DOT) 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,'S AT',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.

Reply With Quote
  #17  
Old   
Malcolm Dew-Jones
 
Posts: n/a

Default Re: Q: something like rownum - 04-17-2008 , 03:16 PM


Charles Hooper (hooperc2000 (AT) yahoo (DOT) com) wrote:
: On Apr 17, 2:26=A0pm, yf... (AT) vtn1 (DOT) 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,'S AT',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.

Reply With Quote
Reply
Page 2 of 2 < 1 2

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top