I am implementing a small queue to handle which process gets to run first. I am using a table in a database to do this. Here is the structure of the table (I'm mocking it up in 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 row of which process can run next. Here is 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 proper order:
select * from queue_manager order by priority_number, timestamp;
This will give me the item with the lowest priority number (most important) at the top, and in those priority numbers, the earliest into the queue (by timestamp) at the top.
I could run this query, and only take the first row, but I would rather do this with a SQL query that would give me the one row of the process that is at the top of the queue (in the example data above, the row with id=7).
I tried doing self joins and sub queries, but I must be having a mental block - I just can't seem to get it right.
Thanks in advance!
EDIT
I forgot to mention that I am looking for a database-independent query. I am mocking this up in SQlite, but there is a good possibility I will implement this in DB2 or Oracle. I had thought to use a "limit 1" type operator on my query, but that is different between different database engines.
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)