Have Geek Brain

Single Query SQL Queue Implementation

I have a SQL table that looks like this:

TABLE QUEUE
    ID
    DATA
    POSITION

The POSITION field stores the record of the record relative to the queue. So it can be:

  • 0

    not in any position (not in the queue).
  • 1

    first in the queue (next in the line).
  • > 1

    position in the queue.

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

Before request:

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

After the request:

  • 09, Data Record 09, 0
  • 10, Data record 10, 0
  • 11, Data Record 11, 1
  • 12, Data Record 12, 2

How can I do this in one request?

0


a source to share


2 answers 


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


+4


a source


Create your queue as a linked list:

id  parent

09  00
10  09
11  10
12  11

and run the following queries:



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 item from the queue, simply update the parent to -1

.

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

+1


a source






More articles:


All Articles