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.
    – Mp0int
    Oct 23, 2019 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, 2019 at 12:10
  • @Squirrel how can I apply cycle of numbers between 1 and 9999 for each UserId?
    – Mp0int
    Oct 23, 2019 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, 2019 at 12:15
  • @Squirrel can you write this with an example insert statement as an answer and I will acceept it thanks (:
    – Mp0int
    Oct 23, 2019 at 12:29

2 Answers 2


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.


I came across your question while trying to figure out something similar. For each user I have, I want to sequentially number all the messages to and from them, but the messages are stored in the same table with all the other users. We're not going to want to create a new 'sequence' (generated by the db) for each new user. Here is what I came up with, applied to your fields, which I will call user_id and user_seq_num, and we'll pretend we're putting them into a table called messages with another column called msg_text

INSERT INTO messages(user_id, user_seq_num, msg_text) 
VALUES ($user_id, 
          (SELECT max(user_seq_num) 
           FROM messages 
           WHERE user_id = $user_id) + 1

The subquery is basically an on-the-fly sequence generator, instead of one you've created ahead of time as a sequence defined and generated by the database, where you ask it for nextval or whatnot. In your case, as stated by other posters, you can add a mod 1000 (% 1000) to the +1 arithmetic. And, this isn't going to work for the first time a new user_id appears in the table. I'm sure there's probably some tricky/cool SQL syntax -based solution that varies a lot depending on the db vendor, but what I'm going to do is:

a) either put a dummy/anchor first entry in my messages table whenever a new user_id is created -- so that it will work upon the first insert done with the above query.

b) before you insert using the above query, do a select query, and if there are no entries yet for that user, simply insert the new row with a user_seq_num of 1; otherwise use the subquery method above.

Hope this helps someone else, since I'm sure you've moved on after 4+ years ; )

BTW, the above query structure works in Postgres. I make no claims for other SQL flavors.

EDIT: Okay, a day later, while actually implementing, I ended up using:

phone_events(phone, phone_event_num, event_type, epoch_time_ms, msg_id, msg_text) 
COALESCE((SELECT max(phone_event_num) FROM phone_events WHERE phone = '${phone}'), 0) + 1, 
'server_sent', ${epoch_time_ms}, '${params.messageId}', '${params.messageText}')

I'd never used COALESCE, but apparently it can convert nulls to values. In discovering this on the web, I learned that the Oracle equivalent is NVAL and apparently COALESCE is ANSI standard SQL. No need for extra work outside the DB, yay!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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