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 create lov dynamically at runtime & attach to text field?

0 Answers  

What is the maximum number of CHECK constraints that can be defined on a column ?

3 Answers  

What is a Garbage Collection? and what is full recursive Garbage collection?

0 Answers   RBS,

What is the max number of columns in an oracle table?

0 Answers  

State the difference between a primary key and foreign key?

0 Answers  

Can u make a synonym for deptno=10 only from emp table.

1 Answers   TCS,

when a grant option is encountered for a table EMP to a peer sitting beside you who has already having the table of that name (EMP), then what is the result?

2 Answers  

In which dictionary table or view would you look to determine at which time a snapshot or MVIEW last successfully refreshed?

1 Answers  

does the query needs a hint to access a materialized view?

0 Answers  

What is blob datatype?

0 Answers  

What is tns service name?

0 Answers  

which sql command we can use to get a print out from oracle?

2 Answers   iFlex, INDUS,