Answer / priyankur

@peter: your query is pretty cool but it wont work when
there are multiple salaries with same amount. I mean three
persons have same salary 24000, which is MAX sal in salary
table. In that case, below query will work.

select a.salary from employees a where 1 = (select count
(distinct b.salary) from employees b where
a.salary<b.salary)

Digit 1 is (n-1) where n is nth value. Here n is 2(Second
highest salary).

I would appreciate if anybody tried to break my above query
and explain how does this work.

Answer / kamal

step 1=write a query for maximum salary.
step 2=make a sub query of step 1
step 2 a)let X==sleect all row from your table(employee)
b)let Y=select maximum salary from employee
NOw c)wrie X-Y


Syntax is:
select MAX(salary) from employeee((select * from
employee)-(selcect MAX(salary) from employee))

Answer / yaseen syed

select *
from
(select empno,ename,sal,rank() over(order by sal desc)
ranking from emp)
where ranking=2;

and u can find the nth highest sal by replacing 2 by your
number

Answer / shalini

SELECT ID,NAME,SAL,ROWNUM
FROM EMP
ORDER BY ID DESC
WHERE ROWNUM=2

Answer / anil

Guys try this to get 2nd highest salary

select max(salary) from employee where salary not in (select
max(salary) from employee)

Answer / mahesh

We can use an OLAP function
select * from
(select ename,sal,dense_rank() over(order by sal desc)
rankings from emp ) a
where a.rankings = 2;

Answer / yobu

select min(marks) from (SELECT sid FROM(SELECT sid FROM STUDENT ORDER BY SId desc) WHERE ROWNUM <=2);

Answer / ved

SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP n salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary

Answer / subash

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

Answer / peter

SQL> select sal
2 from (select sal,
3 row_number() over
4 (order by sal desc ) rn
5 from emp )
6 where rn = 2;

SAL
----------
3000

SQL>

What is set verify off in oracle?

0 Answers  

What should be the return type for a cursor variable.Can we use a scalar data type as return type?

1 Answers  

What happens if you set the sga too low in oracle?

0 Answers  

State all possible different index configurations a table can possibly have?

0 Answers  

How to create id with auto_increment on oracle?

0 Answers  

What is flashback in Oracle?

0 Answers   MCN Solutions,

can u send the sql dumps to sivakumarr1987@gmail.com plz help me

0 Answers  

What is raw datatype?

0 Answers  

if you update view? where we store the data?

5 Answers   Cap Gemini,

how many triggers we can create on a table

11 Answers   G2 Technology, TCS,

6. Display the client name and order date for all orders using the traditional method.

0 Answers   Wipro,

What are the extensions used by oracle reports?

0 Answers