how to sequence number sub gorups

Question

I have a table structure like below

id  wstage      wstatus wdate
101 Unaquired   create  2013-08-29 17:07:20.040
101 Unaquired   rework  2013-08-29 18:07:20.040
101 inprocess   accqui  2013-08-29 19:07:20.040
101 inprocess   alloca  2013-08-29 20:07:20.040
101 Unaquired   create  2013-08-29 21:07:20.040
101 Unaquired   rework  2013-08-29 22:07:20.040

I have to number this like

id  wstage      wstatus wdate                   rownumber
101 Unaquired   rework  2013-08-29 22:07:20.040 1
101 Unaquired   create  2013-08-29 21:07:20.040 1
101 inprocess   alloca  2013-08-29 20:07:20.040 2
101 inprocess   accqui  2013-08-29 19:07:20.040 2
101 Unaquired   rework  2013-08-29 18:07:20.040 3
101 Unaquired   create  2013-08-29 17:07:20.040 3

I am trying to use function

select *,ROW_NUMBER() over (partition by id,wstage order by wdate desc) rownumber

but this is not giving desired output. I dont want to use pl/sql is there a ranking function or simple query to achive this. my table has 50 million records.

Answer 1

Assuming wdate values are unique per id, this might do the job:

WITH partitioned AS (
  SELECT
    *,
    grp = ROW_NUMBER() OVER (PARTITION BY id         ORDER BY wdate DESC)
        - ROW_NUMBER() OVER (PARTITION BY id, wstage ORDER BY wdate DESC)
  FROM atable
),
maxdates AS (
  SELECT
    id, wstage, wstatus, wdate,
    maxwdate = MAX(wdate) OVER (PARTITION BY id, wstage, grp)
  FROM partitioned
)
SELECT
  id, wstage, wstatus, wdate,
  rownumber = DENSE_RANK() OVER (PARTITION BY id ORDER BY maxwdate DESC)
FROM maxdates
;

The first CTE determines distinct id, wstage islands, the second one finds maximum wdate per island, and the main query ranks the rows based on the found maximum values.

A SQL Fiddle demo is available for this query.