Method 1)
Below query can be used to find the nth maximum value, just replace 1 from nth number
SELECT * FROM `emp` e1 where 1 = (select count(distinct(salary)) from emp e2 where e2.salary>e1.salary) |
Method 2)
select * from emp where salary = (select max(salary) from emp where salary < (select max(salary) from emp )) |
Method 3)
if u want the second minimum then just change the max to min and change the less than(<) sign to grater than(>).
select max(salary) from emp where salary<(select max(salary) from emp) |
Method 4)
if u want the second minimum then just change the desc to asc.
select * from emp order BY Salary desc limit 1,1 |
Method 5)
SELECT * FROM `emp` WHERE salary = ( SELECT salary FROM emp e ORDER BY salary DESC LIMIT 1, 1 ) |
Method 6)
SELECT MAX(Salary) FROM emp WHERE Salary NOT IN (SELECT MAX(Salary) FROM emp) |
Method 7)
select MAX(salary) from emp where salary!= (select MAX(salary) from emp ) |