Write a query to get 2nd maximum salary in an employee table ?
Answers were Sorted based on User's Feedback
Answer / niladri saha(oracle apps consu
SELECT EMP_NAME EmployeeName, SAL Salary
FROM EMP
WHERE SAL =
( SELECT SAL
FROM
(
SELECT
DISTINCT SAL
FROM EMP
ORDER BY SAL DESC
)
WHERE ROWNUM=2
);
This Query will list all the employees, having second
highest salary.
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / rajesh
Guess this works...
select max(sal)
from (select * from emp
where sal not in (select max(sal) from emp))
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / paddu
Mr. Ebnezer this is not a comedy site, to do the comedy.You
know the answer, post it.otherwise leave it.
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / mark berlin
-- Solution #3
select distinct salary from (
select salary,
RANK() OVER (order by salary desc NUlls last) as RRANK
FROM employees
)
Where RRANK = 2;
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / sagar
Select Min(Salary) from Curtest where Salary
in (select Top 2 Salary from Curtest order by salary desc)
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / prasanna
select * from emp where 2(select count(distinct sal) from
emp e where sal>=e.sal);
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / karthik ramasamy
Select max(salary) from salarymaster where salary <(select
max(salary) from salarymaster)
OR
SELECT MAX(SALARY) FROM SALARYMASTER WHERE SALARY NOT IN
(SELECT MAX(SALARY) FROM SALARYMASTER)
OR
Select max(salary) from salarymaster where salary<(Select
max(salary) from salarymaster)
Is This Answer Correct ? | 0 Yes | 0 No |
The following solution is for getting 6th highest salary
from Employee table ,
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 6 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
or
SELECT MIN(Sal) FROM TableName
WHERE Sal IN
(SELECT TOP 6 Sal FROM TableName ORDER BY Sal DESC)
Reference:
http://blog.sqlauthority.com/2008/04/02/sql-server-find-nth-highest-salary-of-employee-query-to-retrieve-the-nth-maximum-value/
Is This Answer Correct ? | 0 Yes | 0 No |
ex:
Raj 200
kamal 300
hajka 500
Suresh 200
so 1st max salary is 500,2nd is 300,3rd is 200
we need 2nd maximum only(i.e 300)
Query is below
SELECT MIN(SALARY) FROM EMPLOYEE WHERE SALARY IN (SELECT
DISTINCT TOP 2 SALARY FROM EMPLOYEE ORDER BY SALARY DESC)
I've checked this query.
it will give 2nd maximum value.
if it is 3rd max salary then use TOP 3 instead of TOP 2
if u need detail explanation:
1)
Qry: SELECT DISTINCT TOP 2 SALARY FROM EMPLOYEE ORDER BY
SALARY DESC
Output:500
300
2)
SELECT MIN(SALARY) FROM EMPLOYEE WHERE SALARY IN (SELECT
DISTINCT TOP 2 SALARY FROM EMPLOYEE ORDER BY SALARY DESC)
ans: it gets minimum salary from subquery( from above 1st ans)
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / reddy
select distinct (a.sal) from emp a where &n = (select count
(distinct(b.sal) from emp bmwhere a.sal<=b.sal)
Is This Answer Correct ? | 0 Yes | 0 No |
What is example of database?
What is compilation error in pl sql?
Difference between DBMS and RDBMS...CODD's rules
Are there any features that are decommissioned in 11g that are not present in 11g?
What are different categories of sql commands?
How to fetch common records from two tables?
counting the no.of characters occurs in a string by using pl/sql function
Name the tables where characteristics of Package, procedure and functions are stored ?
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?
we have a package and we have grants to execute that package inside of that we have table, here we don't have privileges to this table? whether this package will execute or not?
Why primary key is required?
What is a constraint? Tell me about its various levels.