Working out the SQL to query a priority queue table

Question

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.

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 such called "database independency", it's a myth for most real world tasks. As a rule, you cannot even create schema in database-independent way.