435,197 Members | 1,210 Online
Need help? Post your question and get tips & solutions from a community of 435,197 IT Pros & Developers. It's quick & easy.

Select 3rd maximum value from table

 P: 78 Hi I have a table named "salary". Expand|Select|Wrap|Line Numbers user_id salary 1          100 2          200 3          500 4          500 5          500 6          700 7          800 8          700 9          800     I need to find out 3rd highest salary from this table. How to write query for that? Aug 6 '13 #1
8 Replies

 P: 7 Try this Expand|Select|Wrap|Line Numbers SELECT * FROM SALARY WHERE salary in (SELECT s.salary FROM SALARY s ORDER BY column DESC LIMIT 2,1) Aug 6 '13 #2

 P: 78 @Basanth thank you for your response LIMIT 2,1 it will results 500. But there are 3 records. Is it possble to do it dynamically.? Aug 6 '13 #3

 P: 7 @Ammu, I didn't fully understand ur question but if want to display all the rows which r equal to 3rd maximum value u can use subquery. I modified my answer check out Aug 6 '13 #4

 P: 78 yes it is working now.I have used "=" instead of "in". thank you Aug 6 '13 #5

 P: 1 Expand|Select|Wrap|Line Numbers SELECT name, salary FROM employees WHERE salary = (SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees)) Jan 2 '14 #6

P: 1
/*for 4th highest salary(for remember think limit 3,1 as 3+1). you can try 5th 0r nth */
SELECT DISTINCT(Salary) FROM employee ORDER BY Salary ASC LIMIT 1,1

/*for 2nd highest salary */
SELECT DISTINCT(Salary) FROM employee ORDER BY Salary DESC LIMIT 1,1

/*for 3rd highest salary */
SELECT DISTINCT(Salary) FROM employee ORDER BY Salary DESC LIMIT 2,1
Attached Images
 Capture.jpg (75.9 KB, 726 views)
Feb 13 '14 #7

 P: 1 For retrieving 3rd highest salary, the following query is quite enough than the P: 7 answer select salary from salary order by salary desc limit 2, 1; Jul 3 '14 #8

 P: 1 using subqueries we can accomplish the task...works in MySQL and Oracle 10g... select max(salary) from table_name where salary<(select max(salary) from table_name where salary<(select max(salary) from table_name));... Sep 23 '14 #9