How to display nth highest record in a table for example ?

Select salary from tablename order by desc limt 4;

Showing Answers 1 - 1 of 1 Answers

sachin0631

  • May 21st, 2009
 

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

here just input the value of 'n' in above query..
like

n=2 to find second highest sal
n=3 to find 3rd highest sal and so on...

cheers...

  Was this answer useful?  Yes

kumarkiranm

  • May 31st, 2010
 

Following query will fetch the record of emp, who is having nth highest sal: 

SELECT * FROM `emp` where sal = (select distinct sal from emp order by sal desc limit n-1,1)

For 1st highest : LIMIT 0,1
For 2nd highest: LIMIT 1,1
so on..


sadaqat

  • Aug 18th, 2010
 

If your table name is emp and the name, sal are two columns of table. then you can easily find out nth highest salary from the table with the help of this query

SELECT DISTINCT sal FROM emp ORDER BY sal DESC LIMIT (n-1),1 ;

If you want to find 3rd highest sal your query will be

SELECT DISTINCT sal FROM emp ORDER BY sal DESC LIMIT 2,1 ;

  Was this answer useful?  Yes

Suppose we have to find records in the emp_salary table with nth highest value for salary.

select * from emp_salary whare salary in(select distinct salary from emp_salary order by salary desc limit n-1,1);

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions