Creating a sequence scheme in MYSQL without AUTO_INCREMENT

Question

I want to run a sequence, scoped to each client so they each have a series of ID's starting from 1:

Clients:
+----+-------+
| id | name  |
+----+-------+
|  1 | Dave  |
|  2 | Sarah |
+----+-------+

Tickets:
+----+-----------+----------+----------------------------+
| id | clientID  | sequence |  title                     |
+----+-----------+----------+----------------------------+
|  1 |         1 |        1 | Title 1                    |
|  2 |         1 |        2 | Another title for Client 1 |
|  3 |         2 |        1 | Different Title            |
+----+-----------+----------+----------------------------+

Whats the best way to do this (in terms of speed and data integrity)? I have something like this upon inserting:

Add new ticket for Dave

SELECT COALESCE( (MAX(sequence) + 1), 1) as nextSeq WHERE id = 1 FROM tickets

then

INSERT INTO tickets (... sequence) VALUES (... nextSeq)

What's the COALESCE for?

When ever the record is first in it's series, the MAX() will return empty, so default empty to 1.

Why I want a user-scoped sequence number?

It's a document capture system, displaying lots of tabular data, I have multiple entities; System, Risk, Ticket, Process and I'm using numbers with titles to help users keep track of their records. Whilst in tables, the initial result will be ordered by this ID, hence I don't want a global ID numbering. I leave the auto_incremented column for other backend operations, joins, private referencing etc.

What is your MySQL server version ? Can you access Window/Analytic functions ? — Madhur Bhaiya
– Madhur Bhaiya, Commented Nov 22, 2018 at 14:46

Arth · Accepted Answer · 2018-11-22 17:00:42Z

2

You could do this as one statement

   INSERT 
     INTO ticket (clientID, sequence, title)
   SELECT clientID,
          MAX(sequence) + 1,
          "Dave's New Ticket"
     FROM ticket
    WHERE clientID = 1 /** Dave */
 GROUP BY clientID

.. but I'm not totally sure about the performance compared to two separate statements. Note, the GROUP BY is actually optional in MySQL

It is also worth questioning whether a sequence number is in itself absolutely necessary, and what you are trying to accomplish with it

edited Nov 22, 2018 at 17:00

answered Nov 22, 2018 at 15:30

Arth

13.1k5 gold badges43 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Creating a sequence scheme in MYSQL without AUTO_INCREMENT

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related