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

As per this question answer, looks no soulution for my question https://stackoverflow.com/questions/21325930/feature-not-implemented-window-order-by — vels4j, Apr 05 '20 at 06:05

GMB · Answer 1 · 2020-04-10T22:34:07.593

3

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

edited Apr 10 '20 at 22:34

answered Apr 03 '20 at 16:11

GMB

216,147
25
84
135

@vels4j: I updated my answer with a solution that does not use window functions. – GMB Apr 06 '20 at 12:50
didnt work, thanks for your effort, I will share you a sql fiddle. looking some online tools with derby – vels4j Apr 06 '20 at 15:25
@vels4j: what do you mean by did not work? Are you getting, an error (whic one)? Or maybe wrong results? – GMB Apr 06 '20 at 15:26
@vels4j: that seems pretty close to what you want. I just added an `order by` clause to the query. – GMB Apr 06 '20 at 15:50
No luck TRANSACTION_ID HSN SEQNO CS610-20-10003 3004 1 CS610-20-10003 3004 1 CS610-20-10003 3304 3 CS610-20-10003 3401 4 – vels4j Apr 07 '20 at 05:39
@vels4j: yes, because these two records have the same transaction_id and hsn, so they get the same row number. Do you have another column that can be used to break the ties? – GMB Apr 07 '20 at 07:19
added table schema here https://ibb.co/Wv4VXwZ line item will have many txnids and many hsn. Idea is to group txnId wise, hsnWise and add seq number. – vels4j Apr 07 '20 at 15:50
@vels4j: when two records have the same `transaction_id` and `hsn`, how do you decide which one is ranked first? In this situation, which column should be use to break the tie? – GMB Apr 07 '20 at 18:25
when you group it by transaction_id and hsn you will get unique then need to asssign seq number – vels4j Apr 08 '20 at 05:43
@vels4j: I added another query to my answer. – GMB Apr 10 '20 at 22:34
no luck, Result of your last query https://ibb.co/MB8pJVF , many thanks for your all the effort – vels4j Apr 16 '20 at 09:49

sql create sequence number in subset - apache derby

1 Answers1