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 view all columns in an existing table?

0 Answers  

What is a select query statement in oracle?

0 Answers  

What are the different types of record groups in oracle? Explain each of them

0 Answers  

What is where clause in oracle?

0 Answers  

What are various constraints used in SQL?

1 Answers   Polaris,

What is ordinary table in oracle?

0 Answers   MCN Solutions,

What is Public Database Link ?

1 Answers  

Is primary key indexed by default in oracle?

0 Answers  

HI Friends Myself Manoj,i am from bengaluru.i have 1 yr of exp in PLSQL but not upto the mark..now attending interviews but m not able to clear even 1 round..i have attended 3 interviews till today.purpose of writing this query is,i want to know the intreview topics that interviewer going to ask frequently,mainly SCENARIO based questions.IF anybody have any PLSQL projects please give it to me,it will helps me alot.Thanks for your patience.

0 Answers  

Why should I use oracle database?

0 Answers  

What is the use of aggregate functions in oracle?

0 Answers  

How to assign query results to variables?

0 Answers