How to find single record in ODBC?

There MUST be a simpler way!

I am trying to come up with the SQL to use (via ODBC) to select a single record out of a table. I need the first record that meets my WHERE but I need to have the table ORDERed BY one of the fields (Priority). .

The goal is having 2 or more applications reading and processing records in my table which is being used as a queue. I want the highest priority record that
hasn't been 'taken' by another application. Of course I want to minimize locking. My plan is to select a single (read only) record that meets my criteria and get it's RecordNumber. Then use that RecordNumber as the key to update the record (only locking a single record).

My best shot so far is:

• create a view ordered by Priority, only containing unmarked records CREATE VIEW myView AS SELECT RecordNumber, ProcessedBy FROM Test WHERE ProcessedBy = " " OR ProcessedBy IS NULL ORDER BY Priority)
• get the RecordNumber of the first (only) record that we've gotten) DECLARE @RecordNumber INT @RecordNumber = (SELECT RecordNumber, ProcessedBy FROM myView WHERE ROWNUM < 2 AND (WHERE ProcessedBy = " " OR ProcessedBy IS NULL))
• mark the record if it hasn't been marked by anybody else. UPDATE Test SET ProcessedBy = "I did it in Oracle" WHERE RecordNumber = @RecordNumber AND (WHERE ProcessedBy = " " OR ProcessedBy IS NULL)

I create a view since it seems you can't use ROWNUM to get the first record of an ordered list.

Are there better ways to do this? Any in standard SQL that will work on other DBs as well (Sybase, SQL Server, Access)?

Thanks...

Garry
garry_star_at_ads-associates.com Received on Tue Apr 18 2000 - 00:00:00 CDT