Select 3rd maximum value from table

[Ammu]

Hi
I have a table named "salary".

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?

[Basanth]

Try this

SELECT * FROM SALARY WHERE salary in
(SELECT s.salary FROM SALARY s ORDER BY column DESC LIMIT 2,1)

[Ammu]

@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.?

[Basanth]

@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

[Ammu]

yes it is working now.I have used "=" instead of "in".
thank you

[anilguptaji]

SELECT name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees))

[vbhatt9]

/*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

[murali13]

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;

[Subham123]

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));...