sql - Select different values from table based on condition -

the table structure below:

++id++++read_id++++read_type   101    201          30   102    201          35   103    201          40   104    201          60   105    202          50   106    202          60 

i need select read_type based on following condition:

condition 1: check each read_id if either 30,35 or 40 present. if present select maximum read_type present among 30, 35 , 40. instance read_id 201 has 30,35,40 , 60. result must 40.

condition 2: if 30, 35 or 40 not present fetch maximum of read_type. instance read_id 202 has 50 , 60. result must 60.

how can achieved single oracle sql query.

you can using conditional aggregation:

select read_id,        (case when sum(case when read_type in (30, 35, 40) 1 else 0 end) > 0              max(case when read_type in (30, 35, 40) read_type end)               else max(read_type)         end) themax t group read_id;