3

For instance, we have a problem of finding the employee with the second highest salary in the table. This is my table

id  name    dept    salary
1   Ram     HR      10000
2   Amrit   MRKT    20000
3   Ravi    HR      30000
4   Nitin   MRKT    40000
5   Varun   IT      50000

Then I would write a subquery like this.

select e_name,salary from employee
    where salary = (select max(salary) from employee
                    where salary <> (select max(salary) from employee));

And I would use limit and offset like this:

select e_name, salary from employee order by salary desc limit 1 offset 1;

Which of the following would be more efficient? and why?

3
  • 3
    What about ties? – Vérace Jun 22 at 19:31
  • 1
    Vérace asks a good question since if the top two had the same salaries, your two solutions will produce different results. – Kirk Woll Jun 22 at 21:59
  • @KirkWoll Its just an example, it does not have to be salary, lets say it is an unique field. – Fakipo Jun 23 at 4:48
6

There's a lot of variables when it comes to performance and query tuning, so there's no guaranteed answer to which one will be more performant for your data at any given time without testing and observing their query plans.

But in general, the second example using LIMIT and OFFSET is likely to be more efficient since it only queries the employee table once as opposed to three times in your first example.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.