CODEWITHSUNDEEP
sqloracle-database
mmartin2409
mmartin2409

Reputation: 54

predicate priority in oracle sql

I have a query in where I need to return only one row from a table based on a descending order of predicate preference. For Example:

SELECT  *
FROM    Tbl
WHERE   col = 1
OR      col = 2 
OR      col = 3;

In the above scenario, if col = 1, I don't want to return data where col = 2 or 3, only data where col = 1.

If col != 1 but col = 2 or 3, i only want to return data for col = 2 and so on.

Is there an easy way to achieve this? Apologies in advance if the way I have explained this is confusing.

EDIT

To clear up the confusion, please see the following sample data:

With tbl As (
Select  1 As RECORD_ID, 'This is the string I want to return' str,                            'FIRST'  priority   From dual Union All
Select  1,              'This is the string i want to return, if CONDITION1 does not exist',  'SECOND'            From dual Union All
Select  1,              'this is the string i will return if i find no others',               'DEFAULT'           From dual Union All
Select  2,              'This is the string i want to return, if CONDITION1 does not exist',  'SECOND'            From dual Union All
Select  2,              'this is the string i will return if i find no others',               'DEFAULT'           From dual
)
Select  RECORD_ID,
        str
From    tbl
Where   xxx;

So in the above example, if I queried for RECORD_ID 1 I would want FIRST string returned, and if I queried for RECORD_ID 2, I should get SECOND.

At the moment, all three strings will return.

Upvotes: 1

Views: 341

Answers (4)

Connor McDonald
Connor McDonald

Reputation: 11581

Depending on the selectivity of each predicate, you may get benefits by separating the query out into individual components, ie,

SQL> select * from
  2  ( select x
  3    from   t1
  4    where  x = :b1   -- first condition
  5    union all
  6    select x
  7    from   t1
  8    where  y = :b1   -- second condition
  9  )
 10  where rownum = 1
 11  /

If the first query in the UNION ALL finds a row, then we can avoid the second query altogether.

You can find a worked example proving the "short circuit" processing here

https://connor-mcdonald.com/2016/03/14/the-first-matching-row/

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

One method uses ordering:

select t.*
from (select t.*
      from t
      where col in (1, 2, 3)
      order by (case col when 1 then 1 when 2 then 2 when 3 then 3 end)
     ) t
where rownum = 1;

Obviously, in your case, the order by could be order by col, but the general situation is to use case.

Upvotes: 1

shawnt00
shawnt00

Reputation: 17915

A standard and common way of doing this is with ROW_NUMBER(). Oracle ROWNUM is not portable:

WITH data AS (
    SELECT t.*, ROW_NUMBER() OVER (ORDER BY LENGTH(priority)) AS rn
    FROM Tbl t WHERE col in (1, 2, 3)
)
SELECT * FROM data WHERE rn = 1;

Here's are other variations, essentially identical, that use min() instead. EDIT: With updated requirements these would tweaks to get the correct ordering but in principle they could still work.

SELECT * FROM Tbl
WHERE col = (SELECT MIN(col) FROM Tbl);

More like the first one:

WITH data AS (
    SELECT t.*, MIN(col) OVER () AS mn
    FROM Tbl t WHERE col in (1, 2, 3)
)
SELECT * FROM data WHERE col = mn;

You might want to look at the query plans to determine which one is the best for you although I suspect your table only has a few rows.

Upvotes: 0

Tony Andrews
Tony Andrews

Reputation: 132570

You could try:

SELECT  t1.*
FROM    Tbl t1
WHERE   t1.col in (1,2,3)
ORDER BY t1.col
FETCH FIRST 1 ROWS ONLY;

Upvotes: 0

Related Questions