SQL development for querying a priority table

Question

I do a small queue to process which process starts first. For this, I use a table in the database. Here is the table structure (I'm mocking SQLite):

"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , "identifier" VARCHAR NOT NULL , "priority_number" INTEGER DEFAULT 15, "timestamp" DATETIME DEFAULT CURRENT_TIMESTAMP, "description" VARCHAR 

I am trying to write SQL to give me the line of the next process. Here are some sample data:

 id identifier priority_number timestamp description 1 test1 15 2009-01-20 17:14:49 NULL 2 test2 15 2009-01-20 17:14:56 NULL 3 test3 10 2009-01-20 17:15:03 NULL 4 test4 15 2009-01-20 17:15:08 NULL 5 test5 15 2009-01-20 17:32:23 NULL 6 test6 14 2009-01-20 17:32:30 NULL 7 test7 7 2009-01-20 17:32:38 NULL 8 test8 20 2009-01-20 17:32:57 NULL 9 test9 7 2009-01-21 13:47:30 NULL 10 test10 15 2009-01-21 13:50:52 NULL 

If I use this SQL, I can get the data in the correct order:

 select * from queue_manager order by priority_number, timestamp; 

This will give me the element with the lowest priority number (most important) at the top and in these priority numbers, the earliest in the queue (by timestamp) at the top.

I can run this query and take only the first row, but I would prefer to do this with an SQL query that would give me one line of the process, which is at the top of the queue (in the above examples, the line with id = 7).

I tried to do self-connections and subqueries, but I have to have a mental block - I just can’t understand what is right.

Thanks in advance!

EDIT

I forgot to mention that I'm looking for a database independent query. I mock it in SQlite, but there is a good opportunity to implement this in DB2 or Oracle. I thought to use the "limit 1" operator in my query, but this is different from the different database mechanisms.

Answer 1

See if this works:

 select * from queue_manager where priority_number = (select min(priority_number) from queue_manager) and timestamp = (select min(timestamp) from queue_manager qm2 where qm2.priority_number = queue_manager.priority_number) 

Answer 2

 select * from queue_manager order by priority_number, timestamp LIMIT 1; 

As for the so-called "database independence," this is a myth for most real world tasks. As a rule, you cannot even create a schema regardless of the database.

Answer 3

If you want it to be "simultaneously secure" on something like InnoDB:

1) Add the field 'in_progress'.

2) Disable AUTOCommit

3) SELECT * FROM queue_manager, where in_progress = 0 order by priority_number, timestamp LIMIT 1 FOR UDPATE;

4) UPDATE queue_manager SET in_progress = 1 where id = X;

5) COMMIT

6) Do the work. Then delete the line when it is done to satisfy. Have a "master process" process / reinstall / clear old in_progress jobs.

Answer 4

The best way to do this is database dependent; it is much easier to have different search procs for different target DBMSs compared to all the overhead costs of cursors or other designs.

Answer 5

Selecting a limited number of rows is done differently in different SQL variants, so depending on what you are using, there may be an integrated way to do this. For example, in MS SQL Server:

 SELECT TOP 1 identifier, priority_number, timestamp, description FROM dbo.Queue_Manager ORDER BY priority_number, timestamp 

To do this, the following methods should work in ANSI-compliant SQL:

  SELECT QM1.identifier, QM1.priority_number, QM1.timestamp, QM1.description FROM Queue_Manager QM1 LEFT OUTER JOIN Queue_Manager QM2 ON QM2.priority_number < QM1.priority_number OR (QM2.priority_number = QM1.priority_number AND QM2.timestamp < QM1.timestamp) /* If you're concerned that there might be an exact match by priority_number and timestamp then you might want to add a bit more to the join */ WHERE QM2.identifier IS NULL 

Or you can try:

 SELECT QM1.identifier, QM1.priority_number, QM1.timestamp, QM1.description FROM Queue_Manager QM1 INNER JOIN ( SELECT priority_number MIN(timestamp) AS timestamp, FROM Queue_Manager WHERE priority_number = ( SELECT MIN(priority_number) FROM Queue_Manager ) GROUP BY priority_number ) SQ1 ON SQ1.priority_number = QM1.priority_number AND SQ1.timestamp = QM1.timestamp 

None of the methods takes into account exact matches in BOTH priority_number and timestamp, so if you think this is possible (and maybe even if you don’t), you need to add a line or two to go to another level using an identifier or something else that guarantees uniqueness. Or just write your front end to handle the random case of returning two lines (maybe just ignore the second - you will get it next time).

Test each method and see what is best for you.

Also, how big do you expect to get a lineup? It would be wise to just query your ORDER BY and get only the first end of the first line.

Answer 6

Read this section and choose the option that will give you the most suitable compatibility. Probably using cursors is the only more or less universal way, but it has some performance limitation that might not be worth it (profile!).

Answer 7

Relational databases are not very good at managing queues.

Try looking at MSMQ in the Windows world, ActiveMQ in the Java world, or Websphere MQ in the business world.

These products do one thing, manage queues, but they do it well.