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>

how to make an oracle object

0 Answers  

I need to get the values of the previous quarter.how to do this?eg: if my cuurent month is may i need to get the datas of the month jan,feb,march.Can it be done in oracle.I tried with date function q but for the month jan its not retriving the previous quarter(oct-dec).how to solve this.plpz anyone help me?

2 Answers   Philips,

diff between DELETE and TRUNCATE?.

14 Answers   HCL, Yalamanchili Software,

Can multiple cursors being opened at the same time?

0 Answers  

Why do you use stored procedures and state some of its disadvantages?

0 Answers  

material view and view disadvantages?

0 Answers  

How are Indexes Update ?

1 Answers  

What is the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN?

1 Answers  

How to initialize variables with default values?

0 Answers  

What are the differences between char and varchar2 in oracle?

0 Answers  

how do u know the total no of rows in a table?

6 Answers  

How to call a trigger inside a stored procedure?Give an example.

2 Answers