1

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?

Improve this question
3
  • Which version of the database? Commented Sep 21, 2017 at 17:00
  • You can try this method to ouput to the file with echo or print the result on a loop. how to store output of sql query in variable in batch script? stackoverflow.com/questions/9891606/… Commented Sep 21, 2017 at 17:14
  • Some sample data and expected results would be helpful. Commented Sep 21, 2017 at 17:25

1 Answer 1

Reset to default
1

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 ;)

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Great! That worked flawlessly. I'll add just one thing: my real data included other fields, so I've finally used SELECT * FROM table WHERE (id, version) in (SELECT id, MIN(version) FROM table WHERE processed = 0 GROUP BY id), which is some tuple-magic I wasn't aware of in SQL. I'm not very SQL-savvy (as you might have noticed), so can I ask why it's important to index that tuple in particular?
@gnzlrm avoid the in(select) if you can, it's usually a disaster for performance... I've edited my answer with other fields. Index allow the DB engine to only load the index for the "search" part of your query you save him a load of all the data of that table.
Oh, sorry I wasn't clear enough when detailing. Some of the extra columns, say amount, can differ from one version to another. I'd have to take the amount from the candidate row, which is what's achieved by using the in (SELECT). Is there any other way to get this without a performance issue?
I've noticed that the addition of field on the GROUP BY clause can have the same benefit, but isn't that optimal than the in (SELECT) up to some point? 'cause I've more than 20 amendable fields. It's still more efficient to do a GROUP BY with all those fields?
@gnzlrm ok, never-mind, I can't find again the way I was doing them. Go with the IN (select), oracle will optimize it as he can. If you really have some performance issue, post again here, you'll have your answer ;)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.