CODEWITHSUNDEEP
sqloracle-database
jochot
jochot

Reputation: 167

Oracle SQL new Column with condition

i have some SQL to do but i can't find how to do that. I have this query: 

SELECT ENAME AS Name, SAL AS Salary FROM EMP WHERE (SELECT max(SAL) FROM EMP) = SAL OR (SELECT min(SAL) FROM EMP) = SAL;

Now I want to have another column which displays if it is Maximum or Minimum SAL.

So that I get something like that:

MaxOrMin  Name    Salary 
Max       Frank   50,000
Max       Peter   50,000
Min       Patric  20,000

Something like that.

Upvotes: 1

Views: 716

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can do this using analytic functions:

select ename as name, sal as salary,
       (case when sal = max(sal) over () then 'max'
             when sal = min(sal) over () then 'min'
        end) as MaxOrMin
from emp;

Upvotes: 6

Related Questions