find out the second highest salary?
Answers were Sorted based on User's Feedback
Answer / shiv prakash
select salary from(select rownum as r,salary from(select
rownum,salary from emp order by salary
desc))where r=2;
Is This Answer Correct ? | 20 Yes | 18 No |
Answer / annada
SELECT e.lastname,e.salary FROM employees e
WHERE 1=(SELECT count(*) FROM employess s
WHERE s.salary>e.salary
that will return 2nd highest salry .if we place insted of 1
as 2 then we can get 3 rd highest salary .
Is This Answer Correct ? | 3 Yes | 1 No |
Answer / chaitanya pathak
select max(salary) from employee
whrere salary<(selsect max(salary) from employee)
Is This Answer Correct ? | 2 Yes | 0 No |
Answer / sandeep
select max(sal) from emp where sal<(select max(sal) from emp);
Is This Answer Correct ? | 2 Yes | 0 No |
Answer / anitha
select min(salary)
from (select distinct salary
from employee
order by salary desc)
where rownum <= 2
Is This Answer Correct ? | 2 Yes | 0 No |
Answer / radhiv kumar
PROC SQL;
SELECT MAX(SALARY) FROM RIGHTMER1
WHERE SALARY < SELECT MAX (SALARY)FROM RIGHTMER1;
QUIT;
Is This Answer Correct ? | 2 Yes | 1 No |
Answer / naveen
selec * from emp e1 (n-1)=(select count(distinct(sal) from
emp e2 where e1.sal<=e2.sal))
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / raghu
second highest salary department wise query would be this
select e.dept_id,e.salary,e.emp_name,e.rank from (select
dept_id,emp_name,salary,dense_rank() over (partition by
dept_id order by salary desc nulls last) rank
from employee)e
where e.rank = 2
Is This Answer Correct ? | 1 Yes | 0 No |
SELECT * FROM 'emp` WHERE salary < ( SELECT max( salary
)FROM emp ) ORDER BY salary DESC LIMIT 1
This is Mysql syntax, if you use Oracle just remove LIMIT 1
and put it ROWNUM=1;
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / ishrat ali
select max(salary) from employee where salary<(select max(salary) from employee);
Is This Answer Correct ? | 1 Yes | 0 No |
How to define a data field as not null?
What is the effect of setting the value "all_rows" for optimizer_goal parameter of the alter session command? What are the factors that affect optimizer in choosing an optimization approach?
What is meant by a deadlock situation?
How to install oracle database 10g xe?
Why cursor variables are easier to use than cursors?
how do u know the total no of rows in a table?
What is the meaning of recursive hints in oracle?
How many different types of indexes we have in oracle?
24 Answers IBM, TCS,
hi friends, I have a table A col as status|NUM and value as open |1 open |2 close |3 close |3 the O/P should be open|close 1 |3 2 |4
Explain about the analyze command in oracle?
How a database is related to tablespaces?
Indexes in oracle ?
3 Answers Keane India Ltd, L&T,