gurujit roy

• Aug 30th, 2005

 

Question : Suppose there are two fields in table(Employee) say Name and Salary and there are in total 100 records in table. Now my query gives the name of an employee whose salary is 10th among 100 salaries. this query should be solve in SQL not in PL/SQL ? 
 
 
ans: the command will be: 
 
select name,salary from employee where rowid=( select Max(rowid) from employee where rownum<=10); 
 
 
 

Visitor

• Sep 19th, 2005

 

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

Jayan

• Oct 22nd, 2005

 

Hi,

Did u mean 10th Max salary among the 100 employees, then your query should be

---

Select min(sal) from (Select sal from (select sal from emp order by sal desc) where rownum <= &n)

---

Here "n" is 10

Regards,

Jayan

P.RAvi

• Oct 26th, 2005

 

More better would be

select ename,salary from (select sal from employee order by sal desc) where rownu=&n;

Atul Takiar

• Nov 9th, 2005

 

select name , salary from ( select * from employees order by salary desc ) where rownum <= 10minus select name , salary from ( select * from employees order by salary desc ) where rownum <= 9

Shasi

• Jan 7th, 2006

 

I think the below mentioned query must satisfy the condition

select ename,sal from (select ename,sal from (select ename,sal from emp order by sal desc) where rownum < 11 order by sal asc) where rownum = 1

The minus query hits the table more times so a bit of performance concern

nileshsingh

 Profile Answers by nileshsingh 

• May 18th, 2006

 

SELECT LEVEL,max(SAL)
FROM EMP
WHERE LEVEL = 10
CONNECT BY PRIOR SAL > SAL
GROUP BY LEVEL;

GG

• Aug 2nd, 2006

 

Suppose we have column 'ename' in table emp, the query may be like this:

Select ename from (select * from (select * from emp order by salary desc) where rownum <= 10) where rownum = 10)

Banker

• Aug 18th, 2006

 

This seems the only correct query

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

100% returning only 10th max salary

sheel kumar handa

• Feb 13th, 2007

 

Hiii to userSELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal); WITH THIS RESULT U GOT ANY NUMBER OF SALARY IN A GIVEN TABLE PLZ TRY IT;S BEST WAY TO FIND ANY NUMBER OF SALARY IN GIVEN TABLE....................... ONLY SIMPLY ENTER THE VALUE OF '" N " &GET ANY POSTION OF SALARY ,....EXAM... IF U WANT 5th SALARY THEN PUT .........N=5... OR U WANT 7th SALARY FROM TABLE PUT ONLY N=7....................and so on .............

GeekQA123

 Profile Answers by GeekQA123 

• Feb 3rd, 2012

 

It will work--No error
select salary,num from (select salary,rownum num from employees order by salary desc)
where num=2;

rohitosu

 Profile Answers by rohitosu 

• Jul 30th, 2012

 

Code
SELECT * FROM  (SELECT employee_id, salary, dense_rank() OVER ( ORDER BY salary DESC)  r 


  FROM employees) a 


  WHERE a.r =10 OR a.r = 11;

abhikum.402

• Aug 13th, 2012

 

Select emp_name from (select *,Dense_RANK()over (order by Emp_sal) SalayRank from Emp_a)A
where SalayRank=10

manjari_ghosh12

 Profile Answers by manjari_ghosh12 

• Sep 1st, 2012

 

Code
SELECT * FROM (SELECT e.*,rownum rnm FROM (SELECT * FROM emp e ORDER BY sal DESC) e) WHERE rnm=10;