Q . How to find nth number of salary from employee table without using the limit.

Brad Johnson. asked, Aug 03 ' 2019

I want to find 3rd highest salary from emplyee table. I can do the same using limit in query as given below. But I want to do the same without using the limit in the query.

SELECT DISTINCT salary FROM employee ORDER BY DESC LIMIT 2,1;

 

  • MySQL

1 Answers

You can do this with the below query.

  SELECT *
   FROM employee emp1
   WHERE ( n - 1 ) = ( SELECT COUNT( emp2.salary )
                   FROM employee emp2
                   WHERE emp2.salary >= emp1.salary
                 )

Here n-1 is the formula just replace n with number of highest salary you want.

check this in Demo SQL Fiddle

 

Leave a Ansewer