Row_Number Sybase SQL Anywhere change on multiple condition

I have a selection that returns

EMP DOC  DATE
  1  78 01/01
  1  96 02/01
  1  96 02/01
  1 105 07/01
  2   4 04/01
  2   7 04/01
  3  45 07/01
  3  45 07/01
  3  67 09/01

And i want to add a row number (il’l use it as a primary id) but i want it to change always when the “EMP” changes, and also won’t change when the doc is same as previous one like:

EMP DOC  DATE  ID
  1  78 01/01   1
  1  96 02/01   2
  1  96 02/01   2
  1 105 07/01   3
  2   4 04/01   1
  2   7 04/01   2
  3  45 07/01   1
  3  45 07/01   1
  3  67 09/01   2

In SQL Server I could use LAG to compare previous DOC but I can’t seem to find a way into SYBASE SQL Anywhere, I’m using ROW_NUMBER to partitions by the “EMP”, but it’s not what I need.

SELECT EMP, DOC, DATE, ROW_NUMBER() OVER (PARTITION BY EMP ORDER BY EMP, DOC, DATE) ID -- <== THIS WILL CHANGE THE ROW NUMBER ON SAME DOC ON SAME EMP, SO WOULD NOT WORK.

Anyone have a direction for this?

Answer

You sem to want dense_rank():

select
    emp,
    doc,
    date,
    dense_rank() over(partition by emp order by date) id
from mytable

This numbers rows within groups having the same emp, and increments only when date changes, without gaps.

Advertisement

Answer