CODEWITHSUNDEEP
oracle-databasecomposite-primary-key
Vikash Talanki
Vikash Talanki

Reputation: 213

Get all records between two different composite primary keys

I have a table which has a composite primary key(pk with multiple columns) in oracle. I am running a SAMPLE query to get random primary keys.

SELECT col1, col2, col3, col4 FROM table SAMPLE ( 0.1 ) WHERE col1 = 'XXXXX' ORDER BY col2, col3, col4;

And a sample output will be something like

XXXXX   1   YYY A
XXXXX   2   ZZZ a
XXXXX   3   AAA b
XXXXX   4   BBB c
XXXXX   5   CCC c
XXXXX   6   DDD c
XXXXX   7   EEE i

Now, I want to get all the rows between first 2 primary keys and then next 2 primary keys

Upvotes: 0

Views: 509

Answers (1)

sticky bit
sticky bit

Reputation: 37472

Well the general pattern would be:

  • If col1 of a row is less than the compared value for col1 then the row is "lower" i.e. more up front if sorted.
  • If col1 of a row is equal to the compared value for col1 and col2 of that row is less than the compared value for col2 then the row is "lower" i.e. more up front if sorted.
  • ..

What would lead to an expression like:

col1 < '?'
 OR col1 = '?'
    AND col2 < '?'
  OR col1 = '?'
     AND col2 = '?'
     AND col3 < '?'
   OR col1 = '?'
      AND col2 = '?'
      AND col3 = '?'
      AND col4 < '?'
    OR col1 = '?'
       AND col2 = '?'
       AND col3 = '?'
       AND col4 = '?'

(I didn't use your values, honestly they confuse me. Replace the question marks appropriately.)

The pattern for the other direction is analog. Just make > out of the <. Then AND the both sub expressions. Don't forget to put parenthesis around each of them, to force the ORs to precede ((< sub expression for <= >) AND (< sub expression for >= >).

You may want to try to use Boolean algebra to simplify that if possible. (This time I'm just too lazy to try and do that. And the pattern is more recognizable that way.) But since this a simple task for the optimizer which might change it if possible and promising anyway, there's not much benefit from it other than maybe a better (human) readability.

Upvotes: 1

Related Questions