Seems I need a complex thing - a way to select only FIRST and LAST records from big resultset.
The query itself is unpredictable - it is built dynamically based on some set of rules. Only the known thing is - it is always selecting data from ~2-4 tables: ORDER, SUBORDER, BATCH, BATCHITEM.
So, query could looks like:
SELECT s.ordnr, s.subOrdId, s.calloff, o.seqnr, ...
FROM SubOrder S, Order O
WHERE s.dept = ? AND s.location = ?
AND o.dept = s.dept AND o.location = s.location
AND o.ordnr = s.ordnr
AND o.status = ? AND o.pline = ? AND ...
AND NOT EXISTS ( SELECT i.ordnr FROM BatchItem I, Batch T WHERE .... )
ORDER BY s.refNo, s.calloff
The WHERE clause is unpredictable (it could be any - depends on configuration) - could be any combination of fields in ORDER or SUBORDER tables.
And the ORDER BY clause is unpredictable - could be any (fields used in ORDER BY usually is not part of key) and there could be duplicate values, so values used in ORDER BY are not unique for 100%. Better to say - one of fields in ORDER BY could be unique but it is not predictable what of.
Such resutset could have a big amount of data and the thing I need - to TAKE ONLY THE FIRST and THE LAST records from such resultset.
And SQL should works for DB2 (target version is 8.2+) and for Oracle (target version is 10gR2+).
My concerns are:
1) loading all the data could be a "heavy" operation (consume memory and affects system performance)
2) executing query with the same WHERE + ORDER BY more than 1 time could be a "heavy" operation
So, that is why I'm thinking to take only first and last records from a resultset.
The first thing I have tried - replaced list of fields to select with aggregate functions like count(*), min(ordnr), max(ordnr) but it fail because of ORDER BY. And seems nor DB2 nor Oracle has FIRST() or LAST() functions :-\
Do you have any hints/ideas on how to solve this task?