I'm using a table to track messages sent in several different channels.

I'd like to be able to track the index of each message on a per-channel basis. 'index' is a forbidden word in SQLite so here I'll use 'id' instead.

Here's an example of the end result that I'm looking for:

│ rowid │ channel_id │ id │       text        │
│     1 │          1 │  1 │ Hello!            │
│     2 │          1 │  2 │ Message text      │
│     3 │          1 │  3 │ Beep boop         │
│     4 │          2 │  1 │ Different channel │
│     5 │          2 │  2 │ So id has reset   │

I should then be able to INSERT INTO messages(channel, text) and have id fill itself.

Normally I'd use id INTEGER NOT NULL PRIMARY KEY or id INTEGER NOT NULL AUTOINCREMENT. However, I believe that this increments the id per-table, not per-channel.

Is there a way that I can make the id increment only such that it creates unique combinations with another column?

1 Answer 1


I'd just create a view on top of the table.

The view uses the analytic function row_number() to provide what you needed. You'll need a column to sort on. That could be your AUTOINCREMENT column.


select channel_id, text
  ,row_number() over (partition by channel_id order by identity_column) as id
from unnamed_table
  • Thanks for your response. What would identity_column be in this case? (view is new terminology to me, sorry!) Jun 13, 2019 at 13:29
  • Additionally, would this view be saved and accessible alongside other tables, or would I need to create it each time I query it? Jun 13, 2019 at 13:30

Your Answer

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

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