Find max and second max salary for a employee table MySQL

Question

Suppose that you are given the following simple database table called Employee that has 2 columns named Employee ID and Salary     Employee   Employee ID    Salary    3            200    4            800    7            450   I wish to write a query select max salary  as max salary  2nd max salary from employee  then it should return    max salary   2nd max salary    800             450   i know how to find 2nd highest salary      SELECT MAX Salary  FROM Employee   WHERE Salary NOT IN  SELECT MAX Salary  FROM Employee     or to find the nth     SELECT FROM Employee Emp1 WHERE  N-1      SELECT COUNT DISTINCT Emp2 Salary   FROM Employee Emp2   WHERE Emp2 Salary  gt  Emp1 Salary    but i am unable to figureout how to join these 2 results for the desired result

User · Answer

Simplest way to fetch second max salary & nth salary

select 
 DISTINCT(salary) 
from employee 
 order by salary desc 
limit 1,1

Note:

limit 0,1  - Top max salary

limit 1,1  - Second max salary

limit 2,1  - Third max salary

limit 3,1  - Fourth max salary

User · Answer

The Best  amp  Easiest solution -    SELECT     max salary  FROM     salary WHERE     salary  lt            SELECT             max salary          FROM             salary

User · Answer

You can just run 2 queries as inner queries to return 2 columns   select    SELECT MAX Salary  FROM Employee  maxsalary     SELECT MAX Salary  FROM Employee   WHERE Salary NOT IN  SELECT MAX Salary  FROM Employee    as  2nd max salary    SQL Fiddle Demo

User · Answer

select   from   Employees where Sal  gt      SELECT     max Sal  FROM     Employees WHERE     Sal  lt            SELECT             max Sal          FROM             Employees

User · Answer

This should work same    SELECT MAX salary  max salary     SELECT MAX salary     FROM Employee    WHERE salary NOT IN         SELECT MAX salary          FROM Employee   2nd max salary FROM Employee

User · Answer

Here is another solution which uses sub query but instead of IN clause it uses  lt  operator  SELECT MAX Salary  From Employees WHERE Salary  lt    SELECT Max Salary  FROM Employees

User · Answer

Here change n value according your requirement   SELECT top 1 amount  FROM        SELECT DISTINCT top n amount      FROM payment      ORDER BY amount DESC   AS temp  ORDER BY amount

User · Answer

I think  It is the simplest way to find MAX and second MAX Salary You may try this way   SELECT MAX Salary  FROM Employee  -- For Maximum Salary   SELECT MAX Salary  FROM Employee WHERE Salary  lt   SELECT MAX Salary  FROM Employee   -- For Second Maximum Salary

User · Answer

This solution will give all employee name and salary who have second highest salary  SELECT name  salary FROM employee WHERE salary         SELECT          salary     FROM employee AS emp     ORDER BY salary DESC     LIMIT 1 1

User · Answer

Highest 3 Salaries in mysql   SELECT Salary FROM  SELECT DISTINCT Salary FROM Employee ORDER BY Salary desc  AS custom LIMIT 3       Highest n Salaries in mysql  just put the value of n   SELECT Salary FROM  SELECT DISTINCT Salary FROM Employee ORDER BY Salary desc  AS custom LIMIT n

User · Answer

select max salary  as first   select salary from employee order by salary desc limit 1  1  as second from employee limit 1    For max salary simply we can use max function  but second max salary we should use sub query  in sub query we can use where condition to check second max salary or simply we can use limit

User · Answer

i think that the simple way in oracle is this   SELECT Salary FROM  SELECT DISTINCT Salary FROM Employee ORDER BY Salary desc  WHERE ROWNUM  lt   2

User · Answer

Select Distinct sal From emp Order By sal Desc Limit 1 1    It will take all Distinct sal  And Limit 1 1 means  leaves top one record and print 1 record

User · Answer

You can write SQL query in any of your favorite database e g  MySQL  Microsoft SQL Server or Oracle  You can also use database specific feature e g  TOP  LIMIT or ROW NUMBER to write SQL query  but you must also provide a generic solution which should work on all database  In fact  there are several ways to find second highest salary and you must know a couple of them e g  in MySQL without using the LIMIT keyword  in SQL Server without using TOP and in Oracle without using RANK and ROWNUM   Generic SQL query   SELECT     MAX salary  FROM     Employee WHERE     Salary NOT IN           SELECT             Max Salary          FROM             Employee          Another solution which uses sub query instead of NOT IN clause  It uses  lt  operator   SELECT     MAX Salary  FROM     Employee WHERE     Salary  lt            SELECT             Max Salary          FROM             Employee

User · Answer

Try  SELECT      SUBSTRING  GROUP CONCAT  Salary    1   LOCATE      GROUP CONCAT  Salary     -1   AS max salary      SUBSTRING  GROUP CONCAT  Salary    LOCATE      GROUP CONCAT  Salary      1   AS second max salary  FROM            SELECT Salary FROM  Employee  ORDER BY Salary DESC LIMIT 0 2      a   Demo here

User · Answer

Try below Query  was working for me to find Nth highest number salary  Just replace your number with nth No  Select DISTINCT TOP 1 salary from   Select DISTINCT TOP  nth No  salary from Employee ORDER BY Salary DESC  Result ORDER BY Salary

User · Answer

If we want to find Employee that gets 3nd highest salary then execute this query  SELECT a employeeid          a salary  FROM   SELECT employeeid                 salary                 Dense rank                    OVER                     ORDER BY salary  AS n         FROM   employee  AS a  WHERE  n   3    What do you want

User · Answer

Find Max salary of an employee SELECT MAX Salary  FROM Employee  Find Second Highest Salary SELECT MAX Salary  FROM Employee  Where Salary Not In  Select MAX Salary  FROM Employee   OR SELECT  MAX Salary  FROM Employee WHERE Salary  lt  gt   SELECT MAX Salary  FROM Employee

User · Answer

This is awesome Query to find the nth Maximum  For example  -    You want to find salary 8th row Salary  Just Changed the indexed value to 8  Suppose you have 100 rows with Salary  Now you want to find Max salary for 90th row  Just changed the Indexed Value to 90   set  n  0  select   from  select      n   n 1  as indexed from employee order by Salary desc t where t indexed   1

User · Answer

try this  select max salary  as first           select salary from employee order by salary desc limit 1  1  as second  from employee limit 1

User · Answer

For second highest salary  This one work for me   SELECT salary FROM employee WHERE salary  NOT IN   SELECT MAX  salary    FROM employee ORDER BY salary DESC   LIMIT 1

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

Max Salary     select max salary  from tbl employee  lt br gt  lt br gt    Second Max Salary     select max salary  from tbl employee where salary  lt    select max salary  from tbl employee

User · Answer

select   from emp where sal   select max sal  from emp where eno in select eno from emp where sal  lt  select max sal from emp        try the above code       if you want the third max record then add another nested query  select max sal from emp  inside the bracket of the last query and give less than operator in front of it

User · Answer

with Common table expression  With cte as    SELECT     ROW NUMBER   Over  Order By Salary Desc  RowNumber      Max Salary  Salary   FROM      Employee  Group By Salary    Select   from cte where RowNumber   2

User · Answer

without nested query  select max e salary  as max salary  max e1 salary  as 2nd max salary  from employee as e left join employee as e1 on e salary    e1 salary group by e salary desc limit 1

User · Answer

Not really a nice query but    SELECT   from       SELECT max Salary  from Employee       as a LEFT OUTER JOIN       SELECT MAX Salary  FROM Employee         WHERE Salary NOT IN  SELECT MAX Salary  FROM Employee    as b ON 1 1

User · Answer

For unique salaries  i e  first can t be same as second    SELECT   MAX  s salary   AS max salary     SELECT      MAX  salary       FROM salaries    WHERE salary  lt  gt  MAX  s salary       ORDER BY salary DESC     LIMIT 1     AS 2nd max salary FROM salaries s   And also because it s such an unnecessary way to go about solving this problem  Can anyone say 2 rows instead of 2 columns  LOL

User · Answer

You can write 2 subqueries like this example  SELECT  select max Salary  from Employee  as max id         select Salary from Employee order by Salary desc limit 1 1  as max 2nd

User · Answer

select      e salary         from             SELECT   FROM             Employee               group by salary              order by salary desc              limit 2             e      order by salary asc      limit 1

User · Answer

q  select   from info order by salary desc limit 1 0      display highest 2 salary   or   q  select   from info order by salary desc limit 1 0      display 2nd highest salary

User · Answer

Try like this  SELECT  select max Salary  from Employee  as MAXinmum   max salary  FROM Employee WHERE salary NOT IN  SELECT max salary   FROM Employee      Or   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   In your case   SELECT DISTINCT column name  FROM table name ORDER BY column name DESC limit 2 1

[mysql] Find max and second max salary for a employee table MySQL

Examples related to mysql

Examples related to sql