By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,197 Members | 1,210 Online
Bytes IT Community
+ Ask a Question
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
  1. user_id salary
  2. 1          100
  3. 2          200
  4. 3          500
  5. 4          500
  6. 5          500
  7. 6          700
  8. 7          800
  9. 8          700
  10. 9          800  
  11.  
I need to find out 3rd highest salary from this table.
How to write query for that?
Aug 6 '13 #1
Share this Question
Share on Google+
8 Replies


P: 7
Try this
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM SALARY WHERE salary in
  2. (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
  1. SELECT name, salary
  2. FROM employees
  3. 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
File Type: jpg 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

Post your reply

Sign in to post your reply or Sign up for a free account.