tariq_oracle

 Profile Answers by tariq_oracle 

• May 9th, 2008

 

select * from emp a
where 5=
(select count(distinct sal) from emp
where a.sal>=b.sal)

pvsp.indian

 Profile Answers by pvsp.indian Questions by pvsp.indian

• Jun 15th, 2008

 

(select * from(select * from emp order by sal) where rownum<6) minus (select * from(select * from emp order by sal) where rownum<5)

Praveen2384

 Profile Answers by Praveen2384 

• Sep 3rd, 2008

 

select sal from emp_table where sal in (select sal from emp_table order by sal) where rownum>N-5;

puspanjali

 Profile Answers by puspanjali 

• Sep 4th, 2008

 

SELECT sal FROM ( SELECT ROWNUM srno , sal FROM ( SELECT DISTINCT sal
FROM emp
ORDER BY ASC ) ) a WHERE a.srno = &N ;

sureshkumar.mtech

 Profile Answers by sureshkumar.mtech Questions by sureshkumar.mtech

• Oct 11th, 2008

 

SQL> select x.* from (
  2  select last_name, salary, rank() over(order by salary desc) as high_salary
  3  from employee)x where x.high_salary = 5;

LAST_NAME      SALARY HIGH_SALARY
---------- ---------- -----------
Martin        1234.56           5

SQL> select x.* from (
  2  select last_name,salary, row_number() over(order by salary desc) as num
  3  from employee)x where x.num = 5;

LAST_NAME      SALARY        NUM
---------- ---------- ----------
Martin        1234.56          5

sureshkumar.mtech

 Profile Answers by sureshkumar.mtech Questions by sureshkumar.mtech

• Oct 11th, 2008

 

display the 5th lowest sal from emp table using Correlated subquery

table have data:
----------------

SQL> select last_name,salary from employee order by salary asc;

LAST_NAME      SALARY
---------- ----------
Cat           1232.78
Martin        1234.56
Black         2334.78
Rice          2344.78
Smith         6544.78
Mathews       6661.78

6 rows selected.

SQL>

select x.* from (
select last_name, salary, rank() over(order by salary asc) as high_salary
from employee)x where x.high_salary = 5;

LAST_NAME      SALARY HIGH_SALARY
---------- ---------- -----------
Martin        1234.56           5

select x.* from (
select last_name,salary, row_number() over(order by salary asc) as num
from employee)x where x.num = 5;

LAST_NAME      SALARY        NUM
---------- ---------- ----------
Martin        1234.56          5

srikanth.cherukupalli

 Profile Answers by srikanth.cherukupalli 

• Feb 29th, 2012

 

select rank,salary from (select rownum as rank, salary from (select distinct(salary) from emp order by salary)) where rank=5;

sampra

 Profile Answers by sampra 

• Mar 6th, 2012

 

select * from Hello h where 5=(select count (distinct amt) from hello where h.amt>amt)

This will give the 5th record from lowest