Archives of the TeradataForum

Message Posted: Tue, 23 Feb 2010 @ 17:37:17 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

You can do it by adding a 1/0 flag to each row that starts a new rank then sum the flags over unbounded preceding to current row:

     select
         lvl2.*,
         sum(is_new_rank) over (order by raw_rank, calendar_date rows
             between unbounded preceding and current row) dense_rank
     from (select
               lvl1.*,
               case when raw_rank = max(raw_rank) over (order by raw_rank,
                            calendar_date rows
                            between 1 preceding and 1 preceding)
                    then 0
                    else 1
                    end is_new_rank
           from (select calendar_date, day_of_week,
                     rank() over (order by day_of_week) raw_rank
                 from sys_calendar.calendar
                 where calendar_date between 1100101 and 1101231) lvl1
                ) lvl2

If you don't need the original rank() results, you can get rid of it entirely:

     select
         lvl1.*,
         sum(is_new_rank) over (order by day_of_week, calendar_date rows
             between unbounded preceding and current row) dense_rank
     from (select
               calendar_date,
               day_of_week,
               case when day_of_week = max(day_of_week) over (order by
                            day_of_week, calendar_date rows
                            between 1 preceding and 1 preceding)
                    then 0
                    else 1
                    end is_new_rank
           from sys_calendar.calendar
           where calendar_date between 1100101 and 1101231
          ) lvl1

Note that you might need to play around a bit if you're also ranking over nulls - I haven't thought through that part.

Dave

	<Prev	Next>		<<First	<Prev	Next>	Last>>