2

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?

GMB
  • 216,147
  • 25
  • 84
  • 135
das9999
  • 35
  • 6

2 Answers2

2

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.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • 1
    Quick and easy, not sure why i haven't tried that (was blind around the row_number) but it worked perfectly (il'l study this function better now). I've used the order by doc and date because i can have same date for multiple docs. Thx a lot! – das9999 Apr 21 '20 at 23:32
0

if performance is not a issue in your case, you can try sth. like:

SELECT tx.EMP, tx.DOC, tx.DATE, y.ID
   FROM table_xxx tx
     join y on tx.EMP = y.EMP and tx.DOC = y.DOC
(SELECT EMP, DOC, ROW_NUMBER() OVER (PARTITION BY EMP ORDER BY DOC) ID
   FROM(SELECT EMP, DOC FROM table_xxx GROUP BY EMP, DOC)x)y
Emre
  • 307
  • 1
  • 8