Answer / nusrath sultana

select * from emp
where &n=(select count(distinct(sal)) from emp b
where emp.sal<=b.sal)

Answer / hari kanth

select sal from(select sal,rank() over(order by sal desc)
rnk from emp) e
where e.rnk=2;
(OR)
select rownum,sal from emp group by sal,rownum
having rownum=2;
(OR)
select rn,sal from( select rownum rn,sal from emp group by
sal,rownum order by sal desc)
where rn=2;

Answer / aravind

select * from empdetails where sal=(select max(sal) from
empdetails where sal<(select max(sal)from empdetails));

Answer / anand

SELECT SALARY FROM (SELECT SALARY FROM emp WHERE ROWNUM < 3
ORDER
BY salary) WHERE ROWNUM < 2

Answer / sikindar

SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal)

Answer / amulya kumar panda

select max(salary) from employee where
salary<(select max(salary) from employee)

Answer / raunaq

to all the people who are using rownum in their queries...

This query will not work if you have more than 1 person
having the second highest salary

Answer / akash

select emp_id,sal from ( select emp_id,sal from emp where
sal < ( select max(sal) from emp) order by sal desc ) where
rownum < 2;

Answer / nagaraju

SELECT LEVEL,MAX(SAL)
FROM EMP
WHERE LEVEL IN(2,3)
CONNECT BY PRIOR SAL>SAL
GROUP BY LEVEL;

Answer / madan kumar

select *from (select empno,deptno,min(sal) from emp
group by empno,deptno,sal
order by sal desc)
where rownum<=1

how to genarate random numbers in oracle for particular row?

3 Answers  

Compare and contrast between sql and sql server and explain its various functions?

0 Answers  

Hi Guys,I have completed my B.com Commerc in the year 2005 and MBA finance in 2008(Correspondance).And I have 3+yrs exp in Accounting and Finance feild.I have worked for TCS for 1.8yrs as Project manage office as a enduser in oracle ERP module.Currently working as Project Adminstartor for CSC.I have joined in Oracle financial classes and I am through with that subject.So my concern is After learning oracle, am in dilemma that will i be eligible to get job in oracle or not?Please advice or Give me suggestions.To kick start my career as fresher in Oracle

0 Answers  

What is the function of Optimizer ?

1 Answers  

if i am using dml statement in function. then i am writing select statement what was the output

2 Answers   MasterCard,

What are the Referential actions supported by FOREIGN KEY integrity constraint ?

1 Answers  

What is Index and different types of Indexes?

7 Answers  

Explain coalesce function?

0 Answers  

when do u go for hash partitioning?

1 Answers  

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

1 Answers  

What are operators in oracle?

0 Answers  

What is Data Block ?

2 Answers