Oracle else in where clause

Question

I need to create a query look like this

select * 
from users
where name = 'A'
 if and only if no result THEN
 name = 'B';

I don't want to get the two rows where first row name = A and second row name = B I need just one of them

is there any way to do that in sql ?

I tried to achive that with not exists or case but couldn't make it work

Thank you

Answer 1

Since 'A' is before 'B' alphabetically then, from Oracle 12, you can use:

SELECT *
FROM   users
WHERE  name IN ('A', 'B')
ORDER BY name
FETCH FIRST ROW WITH TIES;

If you do not want to rely on alphabetic ordering then:

SELECT *
FROM   users
WHERE  name IN ('A', 'B')
ORDER BY CASE name WHEN 'A' THEN 1 ELSE 2 END
FETCH FIRST ROW WITH TIES;

If you want to apply a different ORDER BY clause then use a sub-query:

SELECT *
FROM   (
  SELECT *
  FROM   users
  WHERE  name IN ('A', 'B')
  ORDER BY CASE name WHEN 'A' THEN 1 ELSE 2 END
  FETCH FIRST ROW WITH TIES
)
ORDER BY col1, col2;

In earlier versions, you can use the RANK analytic function (or DENSE_RANK):

SELECT *
FROM   (
  SELECT u.*,
         RANK() OVER (ORDER BY CASE name WHEN 'A' THEN 1 ELSE 2 END) AS rnk
  FROM   users u
  WHERE  name IN ('A', 'B')
)
WHERE  rnk = 1

db<>fiddle here