SQL query to find Nth highest salary from a salary table

Here we can create the MYSQL function for this. nth highest salary from the Employee table.

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

For example, given the above Employee table, the nth highest salary where n = 2 is 200. If there is no nth highest salary, then the query should return null.

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE limitv INT;
    SET limitv = N - 1;
  RETURN (
      Select IFNULL(
        (select Distinct Salary from Employee order by Salary Desc limit limitv, 1),
         NULL
      ) as getNthHighestSalary
  );
END

+-------+--------+
|  name | salary |
+-------+--------+
|   A   | 100    |
|   B   | 200    |
|   C   | 300    |
|   D   | 400    |
|   E   | 500    |
|   F   | 500    |
|   G   | 600    |
+-------+--------+

IF YOU WANT TO SELECT ONLY Nth HIGHEST SALARY THEN:

SELECT DISTINCT salary FROM emp ORDER BY salary DESC LIMIT 1 OFFSET N-1;

IF YOU WANT TO SELECT ALL EMPLOYEE WHO GETTING Nth HIGHEST SALARY THEN:

SELECT * FROM emp WHERE salary = (
   SELECT DISTINCT salary FROM emp ORDER BY salary DESC LIMIT 1 OFFSET N-1
);

select distinct(column_name) from table_name order by column_name desc limit (n-1),1;

MySQL query to find Nth highest salary from a salary table(100% true)

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

To get 2nd highest salary:

SELECT   salary 
FROM     [employees] 
ORDER BY salary DESC 
offset   1 rows 
FETCH next 1 rows only

To get Nth highest salary:

SELECT   salary 
FROM     [employees] 
ORDER BY salary DESC 
offset   **n-1** rows 
FETCH next 1 rows only

Try this solution.

select SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(DISTINCT salary ORDER BY salary DESC),',',3),',',-1) from employees

Let there be table salaries containing

+----------+--------+--------+
| emp      | salary | deptno |
+----------+--------+--------+
| ep1      |     10 | dp1    |
| ep2      |     20 | dp2    |
| ep3      |     30 | dp2    |
| ep4      |     40 | dp1    |
| ep5      |     50 | dp1    |
| ep6      |     60 | dp3    |
| ep7      |     70 | dp3    |
+----------+--------+--------+

By Nested Queries: (where you can change offset 0/1/2... for first, second and third... place respectively)

 select
      *
    from
      salaries as t1 
    where 
      t1.salary = (select 
                   salary
                 from 
                   salaries
                 where 
                 salaries.deptno = t1.deptno ORDER by salary desc limit 1 offset 1);

or might be by creating rank: (where you can change rank= 1/2/3... for first, second and third... place respectively)

SET @prev_value = NULL;
SET @rank_count = 0;
select * from 
(SELECT
  s.*, 
  CASE 
      WHEN @prev_value = deptno THEN @rank_count := @rank_count + 1
      WHEN @prev_value := deptno THEN @rank_count := 1 
      ELSE @rank_count := 1 
  END as rank
FROM salaries s
ORDER BY deptno, salary desc) as t
having t.rank = 2;

I have used Procedure for this query

here getHighestSalary procedure, reports the nth highest salary from the Employee table. If there is no nth highest salary, the query should report null.

first, create table

CREATE TABLE employee (
id INT AUTO_INCREMENT,
salary INT,
PRIMARY KEY (id) );

next, create PROCEDURE

DELIMITER // 
 CREATE PROCEDURE getHighestSalary(emp_id int)
 BEGIN
    select ifnull((select salary from employee where id = emp_id order by salary desc), null) as getNthHighestSalary;
 END //
 DELIMITER ;

and last, call the getHighestSalary procedure with n value

call getHighestSalary(2); -- 200