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.

Answer / rajesh

Guess this works...

select max(sal)
from (select * from emp
where sal not in (select max(sal) from emp))

Answer / paddu

Mr. Ebnezer this is not a comedy site, to do the comedy.You
know the answer, post it.otherwise leave it.

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;

Answer / sagar

Select Min(Salary) from Curtest where Salary
in (select Top 2 Salary from Curtest order by salary desc)

Answer / prasanna

select * from emp where 2(select count(distinct sal) from
emp e where sal>=e.sal);

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)

Answer / ahamed

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/

Answer / basheer

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)

Answer / reddy

select distinct (a.sal) from emp a where &n = (select count
(distinct(b.sal) from emp bmwhere a.sal<=b.sal)

what is a trigger? : Sql dba

0 Answers  

How do I sort a table in sql?

0 Answers  

how to give permission to users that users can update/insert/delete on a row of table on timeing 10am to 6pm only?

4 Answers   TCS,

function can return value ,procedure also return value through out parameter then what is the difference?

3 Answers   3i Infotech,

Difference between table function and pipelined function?

0 Answers  

Is it possible for a table to have more than one foreign key?

0 Answers  

check whether all the emp numbers are indeed unique.

6 Answers  

Are stored procedures compiled?

0 Answers  

How do you modify a trigger?

0 Answers  

How do I write a cron which will run a sql query and mail the results to agroup?

0 Answers  

how many values can the set function of mysql take? : Sql dba

0 Answers  

Can I learn sql in a week?

0 Answers