I have to create sequence numbers based on related user. Like:

║ Row Id     ║ User Id     ║ Sequence Number   ║
║ 1          ║ 1           ║         1         ║
║ 2          ║ 1           ║         2         ║
║ 3          ║ 2           ║         1         ║
║ 4          ║ 2           ║         2         ║
║ 5          ║ 3           ║         1         ║
║ 6          ║ 1           ║         3         ║

I have some prerequisites:

  • Each sequence number sholud be between 1 and 9999. After 9999, it shoud leap to 1 and continue from there. (CYCLE property of Sequence)
  • Sequence numbers should be generated based on User Id. Each UserId should have its own sequence.
  • Sequence numbers should be sequencial. such that for User ID 5: Sequence # 123 sholud be followed by 124, leap or reuse of a number sholud not happen.

So using a Sequence looks pretty right to me. But I failed to add User ID distinction to sequence.

CREATE SEQUENCE [dbo].[seq_SequenceNumber] 
AS [smallint]

How can I add UserId partition to this sequence?

  • Other solutions are also welcome beyond sequence. Oct 23 '19 at 12:00
  • 3
    Do you required the Sequence Number updated in the table ? You can generate the Sequence Number on the fly using row_number() over (partition by [User Id] order by [Row Id])
    – Squirrel
    Oct 23 '19 at 12:10
  • @Squirrel how can I apply cycle of numbers between 1 and 9999 for each UserId? Oct 23 '19 at 12:12
  • 3
    this can easily achieve using the modulus operator % . ( row_number() over (partition by [User Id] order by [Row Id]) - 1) % 10000 + 1
    – Squirrel
    Oct 23 '19 at 12:15
  • @Squirrel can you write this with an example insert statement as an answer and I will acceept it thanks (: Oct 23 '19 at 12:29

This will produce the desired result for a single query.

drop table if exists T1;

create table T1(RowId int, UserId int, Desired_SequenceNumber int);
insert T1
(1, 1, 9997),
(2, 1, 9998),
(3, 2, 1),
(4, 2, 2),
(5, 3, 1),
(6, 1, 9999);   -- Have increased User 1's values to test wrap-around.

    SequenceNumber = ((ROW_NUMBER() over (partition by UserId order by RowId) - 1) % 9999) + 1
from t1
order by RowId;

The ROW_NUMBER built-in function creates a sequential integer for each row in the result. partition by UserId ensures each value of UserId starts counting again at 1. Order by RowId ensures the ordering of SequenceNumber matches the ordering of RowId. The - 1) % 9999) + 1 bit ensures wrap-around at 9999 and starting again at 1, rather than at zero.

If the SequenceNumbers must be written once and consistent for all subsequent reads the above will not work. Then you will need something like

;with NewData as
    select *
            (7, 1),
            (8, 4)
    ) as T(RowId, UserId)
LatestRow as
        RowId = MAX(RowId)
    from T1
    group by UserId
NextSequence as
        Number = (T1.Desired_SequenceNumber + 1) % 9999
    from T1
    inner join LatestRow
        on LatestRow.RowId = T1.RowId
-- insert T1(RowId, UserId, Desired_SequenceNumber)
    Desired_SequenceNumber = ISNULL(NextSequence.Number, 1)
from NewData
left outer join NextSequence
    on NextSequence.UserId = NewData.UserId;

I've structured it using CTEs for ease of exposition. It could be re-factored for performance.

CTE NewData introduces test data without having to manage tables. Replace this with your actual source. Notice that RowId 7 is for an existing user and 8 is for a new user. I've hard-coded RowIds for ease of reference. This is not necessary for this solution. Your tables likely use an IDENTITY and that's OK.

Because the numbers wrap-around the solution must find the most recent value, not the largest value, and increment from there. I use RowId as a proxy for time. CTE LatestRow finds the most recent i.e. largest / MAX() RowId for each existing user. If RowId is not monotonic this solution will not work and some other measure of time will be required. Note that gaps are OK so deletions and IDENTITY / SEQUENCE is acceptable. Depending on your cardinalities performance may be improved by INNER joining to NewData in this CTE.

CTE NextSequence maps the RowId to an existing sequence value and calculates the next sequence number. The modulo (%) does the wrap-around at 9999. I changed my sample data from that in your question so UserId 1 will wrap around. I'm assuming one row per user in the new data. If there are more you can can assign each in-coming row a local sequence within the new data and add & modulo to the existing base sequence number.

The final select gives the data to be inserted. If your real table has an identity omit RowId. Using an outer join allows new users to be inserted. The ISNULL sets new users' sequences to 1.

Since you get to name SEQUENCE objects you could, in a very convoluted way, create a new one for each value of UserId, and try to code which one to pull from for each in-coming new row. As an exercise it would be interesting. As a production system it would be appalling. Don't do this. The meta-coding would be awful and there will be some limit to the number of sequence objects available in a database.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.