SQL: Correct e.g. 1,2,4,5,7 to 1,2,3,4,5 (queue order)

Question

Lets say we got a support-software where customer can simply stand in queue when it's full. The queue-index will increase with ++n for each customer in queue. Index 0 means you're no longer in queue.

However, in a scenario where a customer choose to leave the queue, the other customer after him wont have their queue-index decreased. Lets say his index was 3, and there was total 5 customer in line.

Means: 1,2,3,4,5 led to -> 1,2,4,5 and that's really terrible. Instead, I want it to correct the rest of the queue properly and decrease everyone above the hatch til there's no more hatch.

How do I correct the queue order with a SQL syntax, where it check if there's any hatch, if so, correct it?

Why did you add "sql" here, are you trying to renumber primary keys to be in consecutive sequence? — Lasse V. Karlsen, May 04 '16 at 09:59
I think you should provide the code explaining how you are removing person from queue — Imad, May 04 '16 at 09:59
Why isn't something like `SELECT TOP 1 id FROM queue WHERE sequence > previouslyProcessedSequence ORDER BY sequence` enough to get "the next item in the sequence"? — Lasse V. Karlsen, May 04 '16 at 10:00
@Anonymous My program is much complicated than that and it's not really possible to post an example of it here since it's actually not a customer-queue project. I don't use the queue as primary. That removing part is not a part of my project. All I can do is connect to the db and correct the the queue-order in order to fix it. — Nyprez, May 04 '16 at 10:09
@Lasse V. Karlsen Im using asp.net/C# to access the db(access MS). Isn't it SQL? EDIT: That's probably a good solution, but is it possible to correct the order? — Nyprez, May 04 '16 at 10:09
To "fix the queue" you would basically have to find all the items that have the wrong order and correct each one. — Lasse V. Karlsen, May 04 '16 at 10:10
Maybe I get this wrong: Is the only thing you need a gap-less numbering for a sorted list? — Shnugo, May 04 '16 at 10:14
@LasseV.Karlsen Oh wait, I really need to correct the queue order, because if I just select the TOP, then another customer could get a queue index that he's not suppose to have, for e.g. 3 customer left (1,2,3,4,5) -> (1,5), then the next customer who join will get queue 3(since if calculated amount customer in queue, which will mean he get queue index 3 -> (1,3(new customer),5(older customer)). — Nyprez, May 04 '16 at 10:15
@Shnugo yeah, just recast the numbering 1,2,5,7 to 1,2,3(from 5),4(from 7) etc. — Nyprez, May 04 '16 at 10:17
Btw: It is easy to avoid the problem you've mentioned in your comment. Do not *count* and give the next number (existing: 1 / 5 and you see two elements and want to give a "3" to the next) but rather use `MAX()+1`. In this case you'd find the "5" and return a "6"... — Shnugo, May 04 '16 at 10:30
If you're using the database to create the incrementally autogenerated unique primary key values then you will never reuse 3, you should always get the next sequence number after *previously generated sequence numbers*. — Lasse V. Karlsen, May 04 '16 at 10:31
Are you storing the queue in an array? Why do you have to bother about the positioning at all? In a `List<>` you can remove one item and add a new one at the end. There's even a specialised `Queue<>` class... — Shnugo, May 04 '16 at 10:34
@Shnugo Im not storing the queue in an array, I'm just access the db via the project, calculate every customer in queue-property via sql-syntax in my project. EDIT: I had your solution in my mind, setting the next index MAX+1, but is it actually ok to do so in working life? I'm just thinking what happens if the queue just builds up into much bigger value when customer kept join and leave. Like an exaggerated scenario (23, 36, 432,433, 532) — Nyprez, May 04 '16 at 10:56
Dont be afraid that the numbers will get that big so soon... There's plenty of space... (use the IDENTITY or AUTO-increment as suggested by @LasseV.Karlsen) And once in a while, when there's nobody in the queue, you just reset this to zero... — Shnugo, May 04 '16 at 10:59
@Shnugo ,Lasse V. Karlsen Yeah that's pretty much right. But can I have separate IDENTITY-order for every support-department(using department-ID as foreign key)? I forgot to mention that I got different queue for each department. How do I reset IDENTITY to start from 0 once there's no queue? How does AUTO-increment work? — Nyprez, May 04 '16 at 11:21
@Nyprez, MS-Access has the "AutoNumber", which is the standard ID for normal tables. An `INT`, which is just counting up. You do **not** have to bother about your Departments, as you can use a `WHERE` to filter for a dedicated department. Read here how to set this back - if you ever need this: http://stackoverflow.com/q/20738596/5089204 — Shnugo, May 04 '16 at 11:25

score 2 · Accepted Answer · answered May 04 '16 at 11:04

2

Don't touch the table.

Use a query, and just sort the queue by the increasing autonumber or call-in time (which you propably also have), and then add a row counter to present the queue stand.

answered May 04 '16 at 11:04

Gustav

53,498
7
29
55

What's call-in time and what do you mean with row-counter? Is is possible to give an example code? Sorry i'm still learning about coding. Thanks in advance. – Nyprez May 04 '16 at 11:12
Most call systems record the time and duration for the call. A row counter is a function that numbers the records sequentially - as you have tagged with c#, sql, asp.net, as well as ms-access the options are way to numerous to list here. – Gustav May 04 '16 at 11:35

score 1 · Answer 2 · answered May 04 '16 at 12:35

For a Table names Queue, with fields Customer and Position:

SELECT Queue.Customer, Queue.Position, (select count(customer) from Queue T where T.position<=Queue.Position) AS [Index]
FROM Queue
GROUP BY Queue.Customer, Queue.Position;

Index is the new queue position with no gaps.

Obviously, the Position field has to incremented by 1 when adding new people to the queue - you cannot use Index+1 for the Position of a new customer.

SQL: Correct e.g. 1,2,4,5,7 to 1,2,3,4,5 (queue order)

2 Answers2