Tuesday, 2 January 2018

MySQL | Nth maximum salary in MySQL using LIMIT clause

MySQL supports a LIMIT keyword, which provides pagination capability. We can find the nth highest salary in MySQL without using a subquery.

SELECT salary FROM Employee ORDER BY salary DESC LIMIT N-1, 1;

4th highest salary in MySQL with the LIMIT clause:
-- use database
use abusecore;

-- creating Employee table in MySQL
CREATE TABLE Employee (name varchar(10), salary int);

-- inserting sample data into Employee table
INSERT INTO Employee VALUES ('Mill', 3000);
INSERT INTO Employee VALUES ('Sham', 4000);
INSERT INTO Employee VALUES ('Jack', 3000);
INSERT INTO Employee VALUES ('Pats', 5000);
INSERT INTO Employee VALUES ('Rock', 7000);

-- 4th highest salary in MySQL
SELECT salary FROM Employee ORDER BY Salary DESC LIMIT 3,1;

-- Output:
3000

Nth highest salary in MySQL with the LIMIT clause:

SELECT salary FROM Employee ORDER BY Salary DESC LIMIT n-1,1;

This approach is faster than correlated query approach but its vendor dependent.

No comments:

Post a Comment

Related Posts Plugin for WordPress, Blogger...