gnzlrm gnzlrm - 27 days ago 8
SQL Question

SELECT statement for fetching values from multiple Priority Queues

I'm using an Oracle DB table to store orders that can have multiple events, and each of them has to be processed sequentially based on a

version
field.

The basic structure of this table contains 3 columns:
Order ID
; the aforementioned
Version
; and
Processed
, a simple 1/0
boolean
flag to know if an order version was processed or not.

The processing of said orders is performed on batch, so what I'm struggling with it's writing a
SELECT
statement that gets, for each Order ID, the next unprocessed version on a single result set.

Does anybody have any idea how this could be achieved?

Answer Source

Just this:

SQL Fiddle

Oracle 11g R2 Schema Setup:

Query 1:

SELECT ID, MIN(version) , field1, field2
FROM t 
WHERE processed = 0 
GROUP BY ID, field1, field2

Results:

| ID | MIN(VERSION) |               FIELD1 |               FIELD2 |
|----|--------------|----------------------|----------------------|
|  1 |            2 | field1 char(20)      | field2 char(20)      |
|  2 |            5 | field1 char(20)      | field2 char(20)      |

Query 2:

select id, version, processed, field1, field2 from t

Results:

| ID | VERSION | PROCESSED |               FIELD1 |               FIELD2 |
|----|---------|-----------|----------------------|----------------------|
|  1 |       1 |         1 | field1 char(20)      | field2 char(20)      |
|  1 |       2 |         0 | field1 char(20)      | field2 char(20)      |
|  1 |       3 |         0 | field1 char(20)      | field2 char(20)      |
|  2 |       5 |         0 | field1 char(20)      | field2 char(20)      |
|  2 |      10 |         0 | field1 char(20)      | field2 char(20)      |
|  3 |       4 |         1 | field1 char(20)      | field2 char(20)      |

BTW, don't forget to add a B-TREE index on (processed,version), it'll help for this query ;)