0

I don't know if I am framing the question correctly but let me summarise the problem by giving an example.

SELECT * 
FROM EMPLOYEE 
WHERE EMPLOYEE.TYPE IN ('CEO', 'MANAGER', 'LEAD', 'DEVELOPER')

Here you can see that all the rows will be returned where the values fall inside the mentioned values.

What I want is that if 'CEO' exists in the table, return the records with CEO only. If not, then return the records with 'MANAGER' and so on.

Based on a sequencing logic, only one record should be returned (based on availability).

Also this is a part of a larger SQL query so if there's a solution where this can be achieved in a single line or two will be helpful.

MT0
  • 143,790
  • 11
  • 59
  • 117
Prayas.code
  • 31
  • 1
  • 5
  • Use `CASE` in `WHERE` clause. Check [here](https://stackoverflow.com/questions/206484/sql-switch-case-in-where-clause) answers if you don't know logic, – Marko Ivkovic May 21 '21 at 12:54

1 Answers1

1

You can use rank():

select e.*
from (select e.*,
             rank() over (order by case e.type when 'CEO' then 1 when 'MANAGER' then 2 when 'LEAD' then 3 WHEN 'DEVELOPER' then 4 else 5 end) as seqnum
      from e
      where e.TYPE in ('CEO','MANAGER','LEAD','DEVELOPER')
     ) e
where seqnum = 1;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786