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 difference between truncate and delete?

0 Answers  

what are steps for interface? where is exchange rate defined in which table?

0 Answers  

How to get last row id?

0 Answers  

i have a table with 1 lac of data.i want to insert this data into another table. i want every 5000 of data insert use a commit.using counter and if error is comming in the 5000 of data it rollback.

1 Answers   MasterCard,

How to use subqueries with the in operator using oracle?

0 Answers  

What is a partition in oracle?

0 Answers  

How do you tell what your machine name is and what is its IP address?

0 Answers  

How to add a new column to an existing table in oracle?

0 Answers  

which institute and which faculty is providing best training for oracle sql, plsql in hyderabad

4 Answers  

What is flashback in Oracle?

0 Answers   MCN Solutions,

how table is defined in plsql table? how can i select column from plsql table? can i use select * from plsql table type?

1 Answers  

Explain the use of indexes option in imp command.

0 Answers