So I have a requirement that if a record exists that satisfies both condition A and B then it should be picked up, otherwise default record that satisfies condition B should be picked up.

For e.g., I have data like

My solution is

Select 
(
  CASE WHEN EXISTS
    (select 1 from table_t where Marks>=INPUT_MARKS and Sub=INPUT_SUB)
  THEN
    select Marks from table_t where Marks>=INPUT_MARKS and Sub=INPUT_SUB
  ELSE
    A.Marks
  END
) as Marks,
A.Year,
A.Name
from table_t A
where Sub=INPUT_SUB;

When INPUT_MARKS=90, INPUT_SUB=Maths

I should get 90, 2021, Harry but I get 90, 2019, John which is not a valid data

Clearly I have to apply CASE statements to year and Name as well but if there are thousands of records(as is in my case) the query is too slow.

Simple If Else doesn't work either as this is a sub query to a bigger query so there will be huge redundancy in code.

Please suggest a solution.

CodePudding user response：

You can use the RANK analytic function (then you do not have to query the same table multiple times with EXISTS or UNION ALL):

SELECT t.*,
       RANK() OVER (
         ORDER BY CASE WHEN marks >= 90 THEN 0 ELSE 1 END
       ) AS rnk
FROM   table_t t
WHERE  Sub='Maths'
ORDER BY rnk
FETCH FIRST ROW WITH TIES;

Which, for the sample data:

CREATE TABLE table_t (Name, Marks, Year, Sub) AS
SELECT 'John',     78, 2019, 'Maths' FROM DUAL UNION ALL
SELECT 'Harry',    90, 2021, 'Maths' FROM DUAL UNION ALL
SELECT 'Mohammed', 82, 2020, 'English' FROM DUAL;

Outputs:

NAME	MARKS	YEAR	SUB	RNK
Harry	90	2021	Maths	1

Or, for Oracle 11 and earlier:

SELECT name, marks, year, sub
FROM   (
  SELECT t.*,
         RANK() OVER (
           ORDER BY CASE WHEN marks >= 90 THEN 0 ELSE 1 END
         ) AS rnk
  FROM   table_t t
  WHERE  Sub='Maths'
  ORDER BY rnk
)
WHERE  rnk = 1;

db<>fiddle here

CodePudding user response：

A UNION could work. Test out this one:

WITH dat AS
(
  SELECT 'John'  AS Name
       , 78      AS Marks
       , 2019    AS Year
       , 'Maths' AS Sub
    FROM dual UNION ALL
  SELECT 'Harry'   ,  90, 2021, 'Maths'   FROM dual UNION ALL
  SELECT 'Mohammed',  82, 2020, 'English' FROM dual
)
SELECT Marks
     , YEAR
     , NAME
  FROM dat mrks
 WHERE sub   =  '&INPUT_SUB'
   AND marks >= &INPUT_MARKS
 UNION ALL
SELECT marks
     , YEAR
     , NAME 
  FROM dat mrks
 WHERE sub   =  '&INPUT_SUB'
   AND NOT EXISTS (SELECT 1
                     FROM dat 
                    WHERE marks >= &INPUT_MARKS
                      AND sub   =  '&INPUT_SUB');