Filter rows by value1, if not use the other

Column 1 "Letter": A, B, C Column 2 "Name": Apple, Boat, Cat

I need a query to get the name value if the letter value equal to A. In case that no A, so return the name for B. So, I need to get one record at the end.

Please help

CodePudding user response：

Order by letter and, from Oracle 12, FETCH FIRST ROW ONLY:

SELECT name
FROM   table_name
ORDER BY letter
FETCH FIRST ROW ONLY

or, in earlier versions, order and then filter by the ROWNUM pseudo-column:

SELECT *
FROM   (
  SELECT name
  FROM   table_name
  ORDER BY letter
)
WHERE  ROWNUM = 1

If you only want A or B values then add a WHERE filter:

SELECT name
FROM   table_name
WHERE  letter IN ('A', 'B')
ORDER BY letter
FETCH FIRST ROW ONLY

or:

SELECT *
FROM   (
  SELECT name
  FROM   table_name
  WHERE  letter IN ('A', 'B')
  ORDER BY letter
)
WHERE  ROWNUM = 1

Which, for your sample data, output:

NAME

Apple

NAME
Apple

fiddle