[SOLVED] SQL Sequence count with duplicate values

SQL Sequence count with duplicate values

I have a table of IDs, with some duplicates and I need to create a sequence based on the IDs. I'm trying to achieve the following.

[ROW]  [ID] [SEQID]
  1     11     1
  2     11     2
  3     12     1
  4     13     1
  5     13     2

I'm using an old version of the H2 DB which doesn't have use of windows functions so I have todo this using straight SQL. I have tried joining the table on itself but I'm not getting the result I want as the duplicate values cause issues, any ideas? I have unique identifier in row number, but not sure how to use this to achieve what I want?

SELECT A.ID, COUNT(*) FROM TABLE A
JOIN TABLE B
ON A.ID = B.ID
WHERE A.ID >= B.ID
GROUP BY A.ID;

Solution

Use a subquery that counts the seqid:

select
  t.row, t.id,
  (select count(*) from tablename where id = t.id and row <= t.row) seqid
from tablename t

It's not as efficient as window functions but it does what you expect.
See the demo (for MySql but it's standard SQL).
Results:

| row | id  | seqid |
| --- | --- | ----- |
| 1   | 11  | 1     |
| 2   | 11  | 2     |
| 3   | 12  | 1     |
| 4   | 13  | 1     |
| 5   | 13  | 2     |