Write a query to find second highest salary of an employee.
Answers were Sorted based on User's Feedback
Answer / gautam poonia
SELECT sal
FROM (SELECT sal FROM emp
ORDER BY sal desc)
WHERE RowNum<=2
minus
SELECT sal
FROM (SELECT sal FROM emp
ORDER BY sal desc)
WHERE RowNum<=1
Is This Answer Correct ? | 17 Yes | 1 No |
Answer / cooldude
select max(sal) from Emp where sal < (select max(sal) from
Emp)
Is This Answer Correct ? | 14 Yes | 2 No |
Answer / gvmahesh
I write the query for this question using OLAP functions.
select e1.* from (select ename,sal,rank()
over(order by sal desc) rank from emp) e1
where e1.rank=2;
Is This Answer Correct ? | 5 Yes | 1 No |
Answer / sathish p
select e1.* from (select ename,sal,rank()
over(order by sal) rank from emp) e1
where e1.rank=2;
Is This Answer Correct ? | 3 Yes | 0 No |
Answer / monika
select max(sal) from emp where sal<select max(sal) from emp
Is This Answer Correct ? | 8 Yes | 6 No |
Answer / mubin ahmad
select salary from employee e1 where 2=(select count(*)
from employee e2 where e2.salary>=e1.salary)
Is This Answer Correct ? | 2 Yes | 0 No |
Answer / humayun quaiser
slect max(sal) from emp
where sal not in (select max(sal) from emp);
or
select max(sal) from emp
where sal<(select max(sal) from emp);
or
select distinct sal from emp e
where 1=(select count(distinct sal) from emp
where sal>e.sal);
Is This Answer Correct ? | 2 Yes | 0 No |
Answer / versaites
select top 1 emp_total_sal,emp_name from (
select top 2 emp_total_sal,emp_name from employee_table
ORDER BY emp_total_sal DESC) a
ORDER BY emp_total_sal ASC
Is This Answer Correct ? | 4 Yes | 3 No |
Answer / manoranjan
to select the nth highest salary from emp table
select distinct(a.sal) from emp a where &n=
(select count(distinct(b.sal))from emp b where a.sal<=b.sal);
Is This Answer Correct ? | 3 Yes | 2 No |
Answer / senorita
select ename,salary from (select rownum,ename,salary from
emp
order by desc) where rownum=2;
Is This Answer Correct ? | 3 Yes | 2 No |
What is sql mysql pl sql oracle?
Is left join inner or outer?
How to export the table data (this table having 18 million records) to .csv file. Please tell me is there any faster way to export the data.
Give the structure of the procedure ?
Can we update views in sql?
What is write ahead logging in sql server?
What is a stored procedure in sql with example?
What is rule base and cost base optimizer?
What is the difference between a primary key and a unique key?
What is primary key and foreign key with example?
what is transaction? : Sql dba
What is normalisation and its types?