sql create sequence number in subset - apache derby

Question

Able to generate Sequence number using the following query

CREATE SEQUENCE seqno AS integer
START WITH 1;

SELECT t1.*,
   (NEXT VALUE
    FOR seqno) AS seqno
FROM
  (SELECT l.TRANSACTION_ID,
      l.HSN
   FROM RWLINEITEM l
   WHERE l.TRANSACTION_ID IN ('CS610-20-10003','CS610-20-10002')
   GROUP BY l.TRANSACTION_ID,l.HSN) t1

this gives result

The requirement is to generate sequence number by Transaction and HSN for example

Is there any way to arrive this result. Using derby-10.13.1.1

Answer 1

It seems like Derby does not fully support standard window function row_number().

A typical approach to emulate this is to use a subquery that counts how many rows have the same transaction_id and a smaller hsn, like so:

select 
    transaction_id, 
    hsn, 
    1 + coalesce(
        (
            select count(*) 
            from rwlineitem l1 
            where l1.transaction_id = l.transaction_id and l1.hsn < l.hsn
        ),
        0
    ) seqno 
from rwlineitem l
where transaction_id in ('CS610-20-10003','CS610-20-10002')
order by transaction_id, hsn

Note that if there are duplicates (transaction_id, hsn) tuples, they would get the same seqno. This is similar to how window function rank() works. If you want a unique number, then you can try and add another random sorting criteria:

select 
    transaction_id, 
    hsn, 
    1 + coalesce(
        (
            select count(*) 
            from rwlineitem l1 
            where 
                l1.transaction_id = l.transaction_id 
                and (
                    l1.hsn < l.hsn 
                    or (l1.hsn = l.hsn and random() < 0.5)
                )
        ),
        0
    ) seqno 
from rwlineitem l
where transaction_id in ('CS610-20-10003','CS610-20-10002')
order by transaction_id, hsn