Having problems getting stop number with ROW_NUMBER () OVER - DB2

Question

I am trying to report on activity. I have a table that includes "stop number", but that stop number has nothing to do with the actual stop at which activity took place (stop 1 arrival/departure, stop 2 arrival/departure). So I want to number the stops using ROW_NUMBER ()...

    select 
WH.SEGMENT_NB,
WH.STOP_NB,
ROW_NUMBER () OVER(PARTITION BY WH.SEGMENT_NB, WH.STOP_NB ORDER BY WH.STOP_NB) as Stop,
MAX(CASE WHEN WH.WORK_ACTION_TYPE_ID = 'EAA' 
    THEN WH.CREATE_TS
END) AS Actual_Arrival,
MAX(CASE WHEN WH.WORK_ACTION_TYPE_ID = 'EAD' 
    THEN WH.CREATE_TS
END) AS Actual_Departure,
WH.CREATED_BY_TX AS ENTERED_BY
    from VASDW.TNT_WORK_HISTORY WH
    join VASDW.TNT_WORK_ACTION_TYPE WA on WA.WORK_ACTION_TYPE_ID = WH.WORK_ACTION_TYPE_ID
        and WA.RECORD_DELETE_FG = 'N'
    where WH.WORK_ACTION_TYPE_ID in('EAA','EAD')
    and wh.segment_nb = 15931846
   GROUP BY WH.SEGMENT_NB, WH.STOP_NB, WH.CREATED_BY_TX
   ORDER BY WH.SEGMENT_NB, WH.STOP_NB

I would expect to see this:

   Segment_Nb     STOP_NB     STOP
    15931846       12345       1
    15931846       12345       1
    15931846       23456       2

But it's reading STOP 1,2,1. Any ideas what I need to fix? TIA.

Answer 1

I think that you want:

DENSE_RANK() OVER(PARTITION BY WH.SEGMENT_NB ORDER BY WH.STOP_NB) as Stop

That is:

• STOP_NB should not belong to the partition - you want it for ordering only

• You want DENSE_RANK() to increment only when STOP_NB changes, without gaps