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>

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  

What is a procedure in oracle?

0 Answers  

What is an external table?

0 Answers  

From the database level, how can you tell under which time zone a database is operating?

1 Answers  

consider some table with 4 r 5 columns in that 1 col is DATE type. The data is like that,For each date in that col some 3 fields r there but all the records r having different data. Now i want to display all the columns by performing grouping on the date field ( as SELECTION Operator(*) cannot be used with group function having only one "group by clause". how to do this? can any one help me in finding out the solution plss?

1 Answers  

How to define default values for formal parameters?

0 Answers  

How to use values from other tables in update statements using oracle?

0 Answers  

Do you know about aggregate functions? What is row num function? Can it be used all databases?

1 Answers   Bravura Solutions, Cap Gemini,

which clause we are not used in where clause?

3 Answers   Semantic Space,

what is difference between table and view? where to use views? thanks in advance!

1 Answers  

How many file formats are supported to export data?

0 Answers  

Why do we need oracle client?

0 Answers