ORDER BY DESC और LIMIT का उपयोग करें
कॉलम का nth उच्चतम मान खोजने के लिए, अवरोही क्रम में क्रमबद्ध करें और LIMIT offset, 1 का उपयोग करें।
सिंटैक्स पैटर्न
- पहला उच्चतम:
ORDER BY col DESC LIMIT 1 - दूसरा उच्चतम:
ORDER BY col DESC LIMIT 1, 1 - चौथा उच्चतम:
ORDER BY col DESC LIMIT 3, 1 - nth उच्चतम:
ORDER BY col DESC LIMIT (n-1), 1
व्यावहारिक उदाहरण
टेबल बनाना
mysql> CREATE TABLE NthSalaryDemo (
Id INT NOT NULL AUTO_INCREMENT,
Name VARCHAR(10),
Salary INT,
PRIMARY KEY(Id)
);
वेतन डेटा डालना
mysql> INSERT INTO NthSalaryDemo(Name, Salary) VALUES('Larry', 5700);
mysql> INSERT INTO NthSalaryDemo(Name, Salary) VALUES('Sam', 6000);
mysql> INSERT INTO NthSalaryDemo(Name, Salary) VALUES('Mike', 5800);
mysql> INSERT INTO NthSalaryDemo(Name, Salary) VALUES('Carol', 4500);
mysql> INSERT INTO NthSalaryDemo(Name, Salary) VALUES('Bob', 4900);
mysql> INSERT INTO NthSalaryDemo(Name, Salary) VALUES('David', 5400);
mysql> INSERT INTO NthSalaryDemo(Name, Salary) VALUES('Maxwell', 5300);
mysql> INSERT INTO NthSalaryDemo(Name, Salary) VALUES('James', 4000);
mysql> INSERT INTO NthSalaryDemo(Name, Salary) VALUES('Robert', 4600);
सभी रिकॉर्ड
+----+---------+--------+ | Id | Name | Salary | +----+---------+--------+ | 1 | Larry | 5700 | | 2 | Sam | 6000 | | 3 | Mike | 5800 | | 4 | Carol | 4500 | | 5 | Bob | 4900 | | 6 | David | 5400 | | 7 | Maxwell | 5300 | | 8 | James | 4000 | | 9 | Robert | 4600 | +----+---------+--------+
केस 1: चौथा उच्चतम वेतन
mysql> SELECT * FROM NthSalaryDemo ORDER BY Salary DESC LIMIT 3, 1;
+----+-------+--------+ | Id | Name | Salary | +----+-------+--------+ | 6 | David | 5400 | +----+-------+--------+
केस 2: दूसरा उच्चतम वेतन
mysql> SELECT * FROM NthSalaryDemo ORDER BY Salary DESC LIMIT 1, 1;
+----+------+--------+ | Id | Name | Salary | +----+------+--------+ | 3 | Mike | 5800 | +----+------+--------+
केस 3: पहला उच्चतम वेतन
mysql> SELECT * FROM NthSalaryDemo ORDER BY Salary DESC LIMIT 1;
+----+------+--------+ | Id | Name | Salary | +----+------+--------+ | 2 | Sam | 6000 | +----+------+--------+
केस 4: 8वां उच्चतम वेतन
mysql> SELECT * FROM NthSalaryDemo ORDER BY Salary DESC LIMIT 7, 1;
+----+-------+--------+ | Id | Name | Salary | +----+-------+--------+ | 4 | Carol | 4500 | +----+-------+--------+
डुप्लिकेट मानों को संभालना
यदि डुप्लिकेट वेतन हैं और आप विशिष्ट रैंक चाहते हैं (उदाहरण: दूसरा विशिष्ट उच्चतम), तो उपयोग करें:
SELECT DISTINCT Salary FROM NthSalaryDemo ORDER BY Salary DESC LIMIT 1, 1;
MySQL 8.0+ विंडो फंक्शन दृष्टिकोण
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS rn
FROM NthSalaryDemo
) t WHERE rn = 4; -- 4th highest
