December 7, 2009
Some time ago the following question appeared on the comp.databases.oracle.misc Usenet group: http://groups.google.com/group/comp.databases.oracle.misc/browse_thread/thread/cad8621182ae7d1b
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 TUEbut 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 TUELooks simple, but how to do this in plain SQL?
I provided the following solution:
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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Maxim Demenko provided the following solution:
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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Ken Denny provided the following solution:
select r_num, the_date, to_char(dates.the_date,'DY') DAY
from (select rownum r_num, the_date
from (select the_date from my_table
where to_char(dates.the_date,'DY') IN
('MON','TUE','WED','THU','FRI')
order by the_date)
union
select null r_num, the_date from my_table
where to_char(dates.the_date,'DY') IN ('SAT','SUN'))
order by the_date;
Recent Comments