Queue SQL implementation with a single query.

Question

I have an SQL table that looks like:

TABLE QUEUE
    ID
    DATA
    POSITION

Field POSITION holds the potition of the record relative to a Queue. So, it can be:

• 0 not in any position (not queued).
• 1 first in the queue (next in line).
• > 1 the position in the queue.

I need a (MS) SQL query that will move the queue up one position.

Before the query:

• 09, Record data 09, 0
• 10, Record data 10, 1
• 11, Record data 11, 2
• 12, Record data 12, 3

After the query:

• 09, Record data 09, 0
• 10, Record data 10, 0
• 11, Record data 11, 1
• 12, Record data 12, 2

How can I do it in a single query?

Answer 1

UPDATE QUEUE
SET POSITION = POSITION - 1
WHERE POSITION <> 0

Answer 2

Create your queue as a linked list:

id  parent

09  00
10  09
11  10
12  11

, and query like this:

WITH vq (id, position) AS (
     SELECT  id, 1
     FROM    queue
     WHERE   parent = 0
     UNION ALL
     SELECT  id, v.position + 1
     FROM    queue q, vq v
     WHERE   q.parent = v.id
     )
SELECT  *
FROM    vq

To remove an element from the queue, just update its parent to -1.

This is more efficient than updating all positions of all records in the table.