SQL query to find Nth highest salary from a salary table

Question

some one help me to find out nth highest salary from the salary table in MYSQL

User · Answer

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

User · Answer

For 4th highest salary   select min salary  from  select distinct salary from hibernatepractice employee e order by salary desc limit 4  as e1   For n th highest salary   select min salary  from  select distinct salary from hibernatepractice employee e order by salary desc limit n  as e1

User · Answer

This will work To find the nth maximum number  SELECT      TOP 1   from  SELECT TOP  nth largest no   FROM Products Order by price desc  ORDER BY price asc    For Fifth Largest number  SELECT    TOP 1    from  SELECT TOP  5   FROM Products Order by price desc  ORDER BY price asc

User · Answer

------- --------     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

User · Answer

select distinct column name  from table name order by column name desc limit  n-1  1

User · Answer

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

User · Answer

try this   select MIN sal  from salary where sal in    select sal from salary order by sal desc limit 9

User · Answer

If you want to get all the records of the employees who has third highest salary then you can use this sql query   Table name  salary  select   from salary where salary     select distinct salary from salary order by salary desc limit 2 1

User · Answer

The query to get the nth highest record is as follows   SELECT        FROM      SELECT                FROM         table name     ORDER BY column name ASC     LIMIT N  AS tbl ORDER BY column name DESC LIMIT 1    It s simple and easy to understand

User · Answer

To get nth highest salary you need to first sort data by using ORDER BY and then select the nth highest record using LIMIT with OFFSET   SELECT DISTINCT salary  AS salary FROM tbl salary ORDER BY salary DESC LIMIT 1 OFFSET  n - 1

User · Answer

Try this one for finding 5th highest salary-  SELECT DISTINCT column name  FROM table ORDER BY  column name  desc LIMIT 4 1   for nth salary-  SELECT DISTINCT column name  FROM table ORDER BY  column name  desc LIMIT n-1 1   I have tried it on phpmyadmin panel

User · Answer

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

User · Answer

This is salary table    SELECT  amount FROM  salary  GROUP by amount ORDER BY  amount DESC  LIMIT n-1   1   Or  SELECT DISTINCT amount FROM  salary  ORDER BY  amount DESC  LIMIT n-1   1

User · Answer

SELECT   FROM employe e1 WHERE n-1     SELECT COUNT DISTINCT e2 salary   FROM employe e2 WHERE e2 salary  gt  e1 salary        Where n   highest number of salary like 1 2 3

User · Answer

set  cnt 0  select s   from  SELECT    cnt     cnt   1  AS rank a   FROM one as a  order by a salary DESC  as s WHERE s rank  3       this for 3rd highest salary   for nth replace 3 value  for example 5th highest   set  cnt 0  select s   from  SELECT    cnt     cnt   1  AS rank a   FROM one as a  order by a salary DESC  as s WHERE s rank  5

User · Answer

If you want to find nth Salary from a table  here n should be any thing like 1st or 2nd or 15th highest Salaries   This is the Query for to find nth Salary   SELECT DISTINCT Salary FROM tblemployee ORDER BY Salary DESC LIMIT 1 OFFSET  n-1    If you want to find 8th highest salary  query should be     SELECT DISTINCT Salary FROM tblemployee ORDER BY Salary DESC LIMIT 1 OFFSET 7   Note  OFFSET starts from 0th position  and hence use N-1 rule here

User · Answer

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

User · Answer

Sorting all the records first  do consume a lot of  time  Imagine if the table contains millions of records    However  the trick is to do an improved linear-search   SELECT   FROM Employee Emp1 WHERE  N-1      SELECT COUNT    FROM       SELECT DISTINCT Emp2 Salary      FROM  Employee Emp2     WHERE Emp2 Salary  gt  Emp1 Salary LIMIT N     Here  as soon as inner query finds n distinct salary values greater than outer query s salary  it returns the result to outer query   Mysql have clearly mentioned about this optimization at http   dev mysql com doc refman 5 6 en limit-optimization html  The above query can also be written as   SELECT   FROM Employee Emp1 WHERE  N-1          SELECT COUNT DISTINCT Emp2 Salary       FROM  Employee Emp2     WHERE Emp2 Salary  gt  Emp1 Salary LIMIT N    Again  if the queries are as simple as just running on single table and needed for informational purposes only  then you could limit the outermost query to return 1 record and run a separate query by placing the nth highest salary in where clause  Thanks to Abishek Kulkarni s solution  on which this optimization is suggested

User · Answer

SELECT   FROM Employee Emp1  WHERE  N-1           SELECT COUNT DISTINCT Emp2 Salary        FROM  Employee Emp2      WHERE Emp2 Salary  gt  Emp1 Salary    For each record processed by outer query  inner query will be executed and will return how many records has records has salary less than the current salary  If you are looking for second highest salary then your query will stop as soon as inner query will return N-1

User · Answer

if wanna specified nth highest could use rank method   To get the third highest  use  SELECT   FROM  SELECT  rank     rank   1 AS rank  salary FROM   tbl  SELECT  rank    0  r  order by salary desc   m WHERE rank 3

User · Answer

SET  cnt 0   SELECT s    FROM    SELECT    cnt     cnt   1   AS rank                  a            FROM   one AS a          ORDER  BY a salary DESC  AS s  WHERE  s rank    3

User · Answer

SELECT DISTINCT column name    FROM table name    ORDER BY column name DESC limit N-1 1    where N represents the nth highest salary     Third highest salary    SELECT DISTINCT column name   FROM table name   ORDER BY column name DESC limit 2 1

User · Answer

Try this solution     select SUBSTRING INDEX SUBSTRING INDEX GROUP CONCAT DISTINCT salary ORDER BY salary DESC      3      -1  from employees

User · Answer

finding the highest salary  select MAX Salary  from Employee    finding the 2nd highest salary  Query-1  SELECT MAX Salary  FROM Employee WHERE Salary NOT IN  SELECT MAX Salary  FROM Employee     Query-2  select MAX Salary  from Employee WHERE Salary  lt  gt   select MAX Salary  from Employee     finding the nth highest salary  Query-1  SELECT     This is the outer query part    FROM Employee Emp1 WHERE  N-1         Subquery starts here    SELECT COUNT DISTINCT Emp2 Salary   FROM Employee Emp2 WHERE Emp2 Salary  gt  Emp1 Salary    Query-2  SELECT   FROM Employee Emp1 WHERE  1      SELECT COUNT DISTINCT Emp2 Salary   FROM Employee Emp2 WHERE Emp2 Salary  gt  Emp1 Salary    nth highest salary using the TOP keyword in SQL Server  SELECT TOP 1 Salary FROM         SELECT DISTINCT TOP N Salary       FROM Employee       ORDER BY Salary DESC         AS Emp ORDER BY Salary   Find the nth highest salary in MySQL  SELECT Salary FROM Employee  ORDER BY Salary DESC LIMIT n-1 1   Find the nth highest salary in SQL Server  SELECT Salary FROM Employee  ORDER BY Salary DESC OFFSET N-1 ROW S   FETCH FIRST ROW ONLY   Find the nth highest salary in Oracle using rownum  select   from     select Emp     row number   over  order by Salary DESC  rownumb  from Employee Emp   where rownumb   n     n is nth highest salary     Find the nth highest salary in Oracle using RANK  select   FROM   select EmployeeID  Salary  rank   over  order by Salary DESC  ranking from Employee   WHERE ranking   N

User · Answer

Try this  n would be the nth item you would want to return   SELECT DISTINCT Salary  FROM table ORDER BY Salary DESC LIMIT n 1

[mysql] SQL query to find Nth highest salary from a salary table

Examples related to mysql