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 |
Who is the owner of mysql database?
What type of database is sql?
What is the difference between UNIQUE CONSTRAINT and PRIMARY KEY? 1. There is no difference. 2. A PRIMARY KEY cannot be declared on multiple columns. 3. A UNIQUE CONSTRAINT cannot be declared on multiple columns. 4. A table can have multiple PRIMARY KEYS but only one UNIQUE CONSTRAINT. 5. A table can have multiple UNIQUE CONSTRAINTs but only one PRIMARY KEY.
What are different types of triggers?
What is trigger with example?
How do we use distinct statement? What is its use?
What packages(if any) has oracle provided for use by developers?
What is the source code of a program?
what is 'mysqlshow'? : Sql dba
what eliminate duplicate without using roenum and not
explain normalization concept? : Sql dba
how are rank and dense rank being alloted for column with same values over a particular column