how to find the second highest salary from emp table?
Answers were Sorted based on User's Feedback
Answer / naveed saleh
You can use this for getting nth highest salary from
Employee table as follows
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP n salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
where n > 1 (n is always greater than one)
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / anantkreshna v
select max(sal) from emp_table where sal < (select max(sal)
from emp_table);
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / mona thakur
SELECT *
FROM emp
WHERE salary < (
SELECT max( salary )
FROM emp )
ORDER BY salary DESC
LIMIT 1
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / kishor
select max(salary ) from employee
where
sal<(select max(salary)from employee)
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / vijay shegokar
select * from employee where salary=(select max(salary) from
employee where salary not in (select max(salary) from
employee));
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / vijay kintali
select e1.ename,e1.sal from emp e1 where n=(select count
(distinct e2.sal) from emp e2 where e1.sal<=e2.sal);
Note:n here is nth highest salary.......
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / arvind
select max(salary) from table_name where sal<(select
max(salary) from table_name)
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / bharat puri
select top 2 (salary) from emp
order by salary desc
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / gurvinder
select max(sal)from emp where sal in(select sal from emp minus select max(sal) from emp);
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / sankar sasmal
select distinct sal from emp e where 2=(select count(distinct sal)from emp s where e.sal<=s.sal)
Is This Answer Correct ? | 1 Yes | 0 No |
can we create index on index?
How collections are improve the performance?
what is single byte over head in oracle..?
Which is better cte or subquery?
how to saw triggers output in pl/sql database?
What is the advantage of nosql?
difference between anonymous blocks and sub-programs.
Explain lock escalation? : Transact sql
Can we edit a view in sql?
What is the difference between sum and count in sql?
how do you restrict number of rows for a particular value in a column.For example:there is a table called fruits,having apples,bananas ,papayas.I dont want to have more than 100 apples in that table ,so how can u restrict number of rows for apple to hundred?
what is difference between decode and case function?