max vs limit which is more effective
mysql, sql
Solution
Neither query is the right way to go about this. You are getting MAX(salary) from records with a salary less than MAX(salary) across all records. In other words, the second-highest salary. The way to do that is just this:
SELECT salary FROM table1 ORDER BY salary DESC LIMIT 1,1
If you really do want the max, just do
SELECT MAX(salary) FROM table1
There's no need for subqueries here. Regardless, make sure you have indexed the salary column, or the query will be slow no matter how you run it.
Problem
This might be a very simple question but didn't find the perfect answer. The query is to find the 2nd highest salary which can be done by using max and limit both.. Using MAX ``` select max(salary) from table1 where salary < (select max(salary) from table1); ``` Using limit ``` select salary from table1 where salary < (select max(salary) from table1) order by salary desc limit 1; ``` So which query will be better and less time consuming considering there are 1000's of records. Thanks in advance.