Oracle how to select based on result?

Question

I am writing a select statement in Oracle to return specific results

If there is results type = 4 then only return type 4, else return everything.

Name	Type	color
a	1	blue
b	2	blue
c	3	blue
d	4	blue

Any help will be appreciated! Thank you!

Answer 1

From Oracle 12, you can use:

SELECT *
FROM   table_name
ORDER BY CASE type WHEN 4 THEN 0 ELSE 1 END
FETCH FIRST ROW WITH TIES;

In earlier versions, you can use:

SELECT *
FROM   (
  SELECT t.*,
         RANK() OVER (ORDER BY CASE type WHEN 4 THEN 0 ELSE 1 END) AS rnk
  FROM   table_name t
)
WHERE rnk = 1;

db<>fiddle here

Answer 2

Or you can use CASE in Where clause... (any version)

WITH 
    tbl AS
        (
            SELECT 'a' "NAME",  1 "TYPE",   'blue' "COLOR" From Dual UNION
            SELECT 'b' "NAME",  2 "TYPE",   'blue' "COLOR" From Dual UNION
            SELECT 'c' "NAME",  3 "TYPE",   'blue' "COLOR" From Dual UNION
            SELECT 'd' "NAME",  4 "TYPE",   'blue' "COLOR" From Dual 
       )
SELECT t.* FROM tbl t
WHERE t.TYPE = CASE WHEN Nvl((SELECT Count(*) From tbl WHERE tbl.TYPE = 4), 0) = 0 THEN t.TYPE ELSE 4 END
-- 
-- Result with TYPE = 4
-- NAME       TYPE COLOR
-- d             4 blue
--
-- Result without TYPE = 4
-- NAME       TYPE COLOR
-- a             1 blue
-- b             2 blue
-- c             3 blue