SQL - How to get fifth maximum salary from the table with out using TOP keywork(without any keywords) in s

GeekInterview.com > Tech FAQs > SQL

Go To First | Previous Question | Next Question

SQL | Question 23 of 184

How to get fifth maximum salary from the table with out using TOP keywork(without any keywords) in sql server 2000
Can you please guide me about this?

Total Answers and Comments: 8

Last Update: May 28, 2008 Asked by: satchin

Sponsored Links

Best Rated Answer

—

No best answer available. Please pick the good answer available or submit your answer.

March 26, 2006 20:30:17

Shree

RE: How to get fifth maximum salary from the table wit...

This should work:select max(emp_salary) from emp e1 where (select count(emp_salary) from emp e2 where e2.emp_salary>=e1.emp_salary)=5However if you want to display the corresponding employee info like first name or so,....I suggest using top keyword, as follows:select top 1 emp_name, emp_salary from emp where emp_name != (select top 1 emp_name from emp order by emp_salary desc) order by emp_salary desc

Is this answer useful? Yes | No

March 31, 2006 01:20:16

IrfanAbdulRehman

Member Since: February 2006 Contribution: 2

RE: How to get fifth maximum salary from the table wit...

select sal, deptno, ename, rk from
(select sal,deptno, ename,rank() over(order by sal) rk from emp)
where rk =5

Is this answer useful? Yes | No

May 19, 2006 08:12:01

nileshsingh

Member Since: May 2006 Contribution: 12

RE: How to get fifth maximum salary from the table wit...

select level,max(sal)

from <table_name>

where level = 5

connect by prior sal > sal

group by level

Is this answer useful? Yes | No

September 07, 2006 07:40:50

Sivakumar

RE: How to get fifth maximum salary from the table wit...

Hi,

This will solve your problem.

Use "select salary from emp a where 5 = (select count(distinct(salary)) from emp b where b.salary>= a.salary) "

Thanks & Regards,

Sivakumar

Is this answer useful? Yes | No

February 02, 2007 02:40:16

Rajeev

RE: How to get fifth maximum salary from the table wit...

Select Max(Salary) from table_Name Where Salary Not IN (Select TOP 5 From Table_Name order by salary desc)

Is this answer useful? Yes | No

April 09, 2007 04:24:59

mukesh kumar tiwari

RE: How to get fifth maximum salary from the table wit...

select salary from tablename group by salary dsc limit 4,1;

Is this answer useful? Yes | No

May 09, 2008 03:44:58

Imran_Javed

Member Since: May 2008 Contribution: 23

RE: How to get fifth maximum salary from the table with out using TOP keywork(without any keywords) in sql server 2000Can you please guide me about this?

SELECT *
FROM (SELECT ename ,sal,
dense_rank() over (ORDER BY sal DESC) max_sal
FROM emp)
WHERE max_sal= 5;

Is this answer useful? Yes | No

May 28, 2008 10:26:46

monika1985

Member Since: April 2008 Contribution: 3

RE: How to get fifth maximum salary from the table with out using TOP keywork(without any keywords) in sql server 2000Can you please guide me about this?

select sal from emp e
where 4=(select count(distinct(sal) from emp where sal>e.sal)

Is this answer useful? Yes | No

Go To Top