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)

When we give SELECT * FROM EMP; How does oracle respond?

24 Answers   Accenture, HCL, Infosys,

write a procedure to print a statement or number not using "dbms_output.put_line" package.write a procedure instead of it using procdure name as "print" ex:- declare a number:=2; begin print(a); end; /* when U type above procedure 2 have to should be printed*/

2 Answers   iFlex,

how can we encrypt and decrypt a data present in a mysql table using mysql? : Sql dba

0 Answers  

What is a stored procedure in sql with example?

0 Answers  

If 100 tables are there in user_tables.I want to find in which table zero records are there with table name.Is it possible?

2 Answers  

Why do we use procedures in pl sql?

0 Answers  

What is memory optimized table?

0 Answers  

Suppose There is a string A.B....C.......D.........E........F In this string dots (.) are not having fixed count in between of string. I want the output to have string with one dot between. I.e. A.B.C.D.E.F

6 Answers   IBM,

How to count the no of records of a table without using COUNT function?

11 Answers   TCS, Tesco,

How do you copy a table in sql?

0 Answers  

How many sql commands are there?

0 Answers  

Explain how you can copy a file to file content and file to pl/sql table in advance pl/sql?

0 Answers