How to find third or n maximum salary from salary table

Question

How to find third or nth maximum salary from salary table EmpID  EmpName  EmpSalary  in optimized way

User · Accepted Answer

Use ROW NUMBER if you want a single  or DENSE RANK for all related rows    WITH CTE AS       SELECT EmpID  EmpName  EmpSalary             RN   ROW NUMBER   OVER  ORDER BY EmpSalary DESC      FROM dbo Salary   SELECT EmpID  EmpName  EmpSalary FROM CTE WHERE RN    NthRow

User · Answer

Find Nth highest salary from a table  Here is a way to do this task using dense rank   function     select linkorder from u links  select max linkorder  from u links  select max linkorder  from u links where linkorder  lt   select max linkorder  from u links   select top 1 linkorder         from   select distinct top 2 linkorder from u links order by linkorder desc  tmp  order by linkorder asc   DENSE RANK   1  DENSE RANK computes the rank of a row in an ordered group of rows and returns the rank as a NUMBER  The ranks are consecutive integers beginning with 1  2  This function accepts arguments as any numeric data type and returns NUMBER  3  As an analytic function  DENSE RANK computes the rank of each row returned from a query with respect to the other rows  based on the values of the value exprs in the order by clause  4  In the above query the rank is returned based on sal of the employee table  In case of tie  it assigns equal rank to all the rows   WITH result AS         SELECT linkorder  DENSE RANK   OVER   ORDER BY linkorder DESC   AS  DanseRank  FROM u links    SELECT TOP 1 linkorder FROM result WHERE DanseRank   5

User · Answer

declare  nHighestSalary as int set  nHighestSalary   3 SELECT TOP 1 salary FROM      SELECT TOP  nHighestSalary salary     FROM employees     ORDER BY salary DESC  AS emp  ORDER BY salary ASC

User · Answer

For nth highest salary    SELECT DISTINCT Salary FROM EMP E WHERE n   SELECT COUNT DISTINCT SALARY   FROM EMP WHERE E SALARY  lt   SALARY    n being the highest value you want  i e 2 3 etc

User · Answer

To query the nth highest bonus  say n 10  using AdventureWorks2012  Try Following code  USE AdventureWorks2012   GO  SELECT   FROM Sales SalesPerson  GO  DECLARE  grade INT  SET  grade   10  SELECT MIN Bonus  FROM  SELECT TOP   grade  Bonus FROM  SELECT DISTINCT Bonus  FROM Sales SalesPerson  AS a ORDER BY Bonus DESC  AS g

User · Answer

SELECT EmpSalary  FROM salary table  GROUP BY EmpSalary  ORDER BY EmpSalary DESC LIMIT n-1  1

User · Answer

Showing all 3rd highest salary    select   from emp where sal   SELECT DISTINCT sal FROM emp ORDER BY sal DESC LIMIT 3 1      Showing only 3rd highest salary   SELECT DISTINCT sal FROM emp ORDER BY sal DESC LIMIT 3 1

User · Answer

finding Nth max value using CTE and FIRST VALUE function  -- 5th max salary   WITH CTE NTH SAL AS       SELECT FIRST VALUE ESAL  OVER ORDER BY ESAL DESC  AS ESAL          1 AS ID     FROM EMPLOYEE     UNION ALL     SELECT FIRST VALUE EMP ESAL  OVER ORDER BY EMP ESAL DESC  AS ESAL          ID     FROM EMPLOYEE EMP           SELECT ESAL          ID 1 AS ID         FROM CTE NTH SAL  CTE NTH SAL         WHERE EMP ESAL lt CTE NTH SAL ESAL                 AND CTE NTH SAL ID lt  5       SELECT DISTINCT ESAL FROM CTE NTH SAL WHERE ID 5   for sample result set and more ways clickhere

User · Answer

select   from employee order by salary desc    ------ ------ ------ -----------    id     name   age    salary       ------ ------ ------ -----------       5   AJ       20   100000 00        4   Ajay     25    80000 00        2   ASM      28    50000 00        3   AM       22    50000 00        1   AJ       24    30000 00        6   Riu      20    20000 00    ------ ------ ------ -----------      select distinct salary from employee e1 where  n     select count  distinct salary    from employee e2 where e1 salary lt  e2 salary    Replace n with the nth highest salary as number

User · Answer

SELECT Salary EmpName FROM   SELECT Salary EmpName DENSE RANK   OVER ORDER BY Salary DESC  Rno from EMPLOYEE   tbl WHERE Rno 3

User · Answer

--nth highest salary          select    from  select lstName  salary  row number   over  order by salary desc  as rn        from employee  tmp where rn   2   -- nth -1   highest salary  select    from employee e1 where 1    select count distinct salary               from employee e2            where e2 Salary  gt  e1 Salary

User · Answer

In SQL Server 2012   OFFSET   FETCH would be an efficient way to achieve this   DECLARE  N AS INT  SET  N   3   SELECT     EmpSalary FROM     dbo Salary ORDER BY     EmpSalary DESC OFFSET   N-1  ROWS FETCH NEXT 1 ROWS ONLY

User · Answer

you can find n  th salary from table if you want to retrive 2nd highest salary then put n 2 if 3rd hs then out n 3 as so on    SELECT    FROM tablename t1  WHERE  N-1     SELECT COUNT DISTINCT t2 Salary     FROM tablename t2 WHERE t2 Salary  gt  t1 Salary

User · Answer

set  n    n  SELECT a   FROM   select a      rn    rn 1  from EMPLOYEE order by a EmpSalary desc   As a  where rn    n

User · Answer

By subquery   SELECT salary from  SELECT rownum ID  EmpSalary salary from  SELECT DISTINCT EmpSalary from salary table order by EmpSalary DESC  where ID   nth

User · Answer

Just change the inner query value  E g  Select Top  2   from Student Info order by ClassID desc   Use for both problem   Select Top  1   from     Select Top  1   from Student Info order by ClassID desc    as wsdwe order by ClassID

User · Answer

In 2008 we can use ROW NUMBER   OVER  ORDER BY EmpSalary DESC  to get a rank without ties that we can use   For example we can get the 8th highest this way  or change  N to something else or use it as a parameter in a function if you like   DECLARE  N INT   8  WITH rankedSalaries AS   SELECT EmpID  EmpName  EmpSalary   RN   ROW NUMBER   OVER  ORDER BY EmpSalary DESC  FROM salary   SELECT EmpID  EmpName  EmpSalary FROM rankedSalaries WHERE RN    N    In SQL Server 2012 as you might know this is performed more intuitively using LAG

User · Answer

If you want optimize way means use TOP Keyword  So the nth max and min salaries query as follows but the queries look like a tricky as in reverse order by using aggregate function names   N maximum salary   SELECT MIN EmpSalary  FROM Salary WHERE EmpSalary IN SELECT TOP N EmpSalary FROM Salary ORDER BY EmpSalary DESC     for Ex  3 maximum salary   SELECT MIN EmpSalary  FROM Salary WHERE EmpSalary IN SELECT TOP 3 EmpSalary FROM Salary ORDER BY EmpSalary DESC     N minimum salary   SELECT MAX EmpSalary  FROM Salary WHERE EmpSalary IN SELECT TOP N EmpSalary FROM Salary ORDER BY EmpSalary ASC    for Ex  3 minimum salary   SELECT MAX EmpSalary  FROM Salary WHERE EmpSalary IN SELECT TOP 3 EmpSalary FROM Salary ORDER BY EmpSalary ASC

User · Answer

Try this one     SELECT MAX salary  FROM employee WHERE salary NOT IN  SELECT   FROM employee ORDERBY salary DESC LIMIT n-1

User · Answer

SELECT TOP 1 salary FROM      SELECT TOP 3 salary     FROM employees     Group By salary ORDER BY salary DESC   AS emp  ORDER BY salary ASC

User · Answer

Subqueries always take more time   use below query to get the any highest and lowest data   Highest Data  select   from business order by id desc limit 3 1    Lowest data  select   from business order by id asc limit 3 1    Can use N in the place of 3 to get nth data

User · Answer

SELECT MIN COLUMN NAME  FROM                SELECT DISTINCT TOP 3     COLUMN NAME            FROM   TABLE NAME            ORDER BY                   COLUMN NAME        DESC          AS  COLUMN NAME

User · Answer

Optimized way  Instead of subquery just use limit   select distinct salary from employee order by salary desc limit nth  1    See limit syntax here http   www mysqltutorial org mysql-limit aspx

User · Answer

SELECT TOP 1 salary  FROM   employee  WHERE  salary IN  SELECT DISTINCT TOP 3 salary                    FROM   employee                    ORDER  BY salary DESC   ORDER  BY salary ASC

User · Answer

For n-th highest value  select min salary   from  select salary from select salary from employee order by salary desc where rownum lt  n

User · Answer

Too simple if you use the sub query   SELECT MIN EmpSalary  from   SELECT EmpSalary from Employee ORDER BY EmpSalary DESC LIMIT 3      You can here just change the nth value after the LIMIT constraint   Here in this the Sub query Select EmpSalary from Employee Order by EmpSalary DESC Limit 3  would return the top 3 salaries of the Employees  Out of the result we will choose the Minimum salary using MIN command to get the 3rd TOP salary of the employee

User · Answer

SELECT   FROM  select distinct Salary from Customers order by salary DESC    limit 4 1   Limit 4 1 means leave first 4 rows and then select the next one   Limit and rownumber depends on the platform you are using   Try this it will work

User · Answer

To find the 5th highest Salary   Declare  N INT   5  SELECT Salary FROM Employee  ORDER BY Salary DESC OFFSET  N - 1 ROW

User · Answer

select    from emp x where  amp no  select count    from emp y where y sal gt  x sal     this will give take input from user and then will tell the nth maximum number I have taken example of emp table in oracle and to display nth maximum salaried employee info  Output Enter value for no  5   EMPNO ENAME      JOB       MGR        HIREDATE  SAL        COMM       DEPTNO  ----- ---------- --------- ---------- --------- ---------- ---------- ----------  7698  BLAKE      MANAGER   7839       01-MAY-81 3000                  30  7788  SCOTT      ANALYST   7566       19-APR-87 3000                  20  7902  FORD       ANALYST   7566       03-DEC-81 3000                  20   Enter value for no  14   EMPNO ENAME      JOB       MGR        HIREDATE  SAL        COMM       DEPTNO  ----- ---------- --------- ---------- --------- ---------- ---------- ----------  7369  SMITH      CLERK     7902       17-DEC-80 800                   20

User · Answer

SELECT TOP 1 salary FROM   SELECT TOP n salary FROM employees ORDER BY salary DESC Group By salary   AS emp ORDER BY salary ASC    where n for nth maximum salary

User · Answer

Optimized way    With CTE as   select salary           RANK order by salary desc  as Rnk from table   select   from CTE where Rnk    yourVariable   Second Opinion   Declare  yourVariable int   2 Select top 1   from    select distinct top  yourVariable   salary  from Employees order by salary desc  as a order by a salary asc select   from Employees order by salary desc

User · Answer

declare  maxNthSal as nvarchar 20  SELECT TOP 3  maxNthSal GRN NAME FROM GRN HDR   ORDER BY GRN NAME DESC print  maxNthSal

User · Answer

select  a GRN NAME  from GRN HDR a GRN HDR b  where a GRN NAME lt  b GRN NAME  group by a GRN NAME  having count a GRN NAME  3

User · Answer

Third or nth maximum salary from salary table without using subquery   select salary from salary    ORDER   BY salary DESC    OFFSET  N-1 ROWS    FETCH NEXT 1 ROWS ONLY   For 3rd highest salary put 2 in place of N-1

User · Answer

Refer following query for getting nth highest salary  By this way you get nth highest salary in MYSQL  If you want get nth lowest salary only you need to replace DESC by ASC in the query

User · Answer

Replace N with your Max Number  SELECT   FROM Employee Emp1 WHERE  N-1      SELECT COUNT DISTINCT Emp2 Salary   FROM Employee Emp2 WHERE Emp2 Salary  gt  Emp1 Salary    Explanation  The query above can be quite confusing if you have not seen anything like it before     the inner query is what   s called a correlated sub-query because the inner query  the subquery  uses a value from the outer query  in this case the Emp1 table  in it   s WHERE clause   And Source

User · Answer

Hi One more example to write this using common table expression    with cte as  SELECT TOP 3 salary     FROM salary    ORDER BY salary DESC     select top 1 salary from cte     with cte as  select e Name s salary  DENSE RANK   over   order by salary desc  sal rank  from salary s left join employee e on s EmpID e EmpID  select Name salary from cte where sal rank 3

User · Answer

select max sal   from emp  where sal  gt        select max sal       from emp      where sal  gt   select max sal  from emp

User · Answer

MySQL tested solution  assume N   4   select min CustomerID  from  SELECT distinct CustomerID FROM Customers order by CustomerID desc LIMIT 4  as A    Another example   select min country  from  SELECT distinct country FROM Customers order by country desc limit 3

User · Answer

Try this   SELECT TOP 1 salary FROM      SELECT TOP 3 salary     FROM employees     ORDER BY salary DESC  AS emp  ORDER BY salary ASC   For 3 you can replace any value

User · Answer

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

User · Answer

This is mysql query for fetching the nth highest salary from the table emp  We use  LIMIT  here as an alternative to  TOP  and the parameter  n-1  shows after which row to start from and the second parameter shows how many rows  to show starting from the  n-1 th row    LIMIT  accepts single parameter also which represents the number of rows to show starting from 0th or 1st    select distinct   from emp order by sal desc limit  n-1  1

User · Answer

To get third highest value from table   SELECT   FROM tableName ORDER BY columnName DESC LIMIT 2  1

User · Answer

You can try this   select top 1  EXPORT NO from DC HDR  order by CASE when   ROW NUMBER   over order by EXPORT NO desc   3 then EXPORT NO else 0 end desc

User · Answer

Try this Query  SELECT DISTINCT salary FROM emp E WHERE  amp no   SELECT COUNT DISTINCT salary   FROM emp WHERE E salary  lt   salary    Put n  which value you want

User · Answer

Try this code  -  SELECT      FROM one one1    WHERE   n       SELECT COUNT  one2 salary                      FROM one one2                    WHERE one2 salary  gt   one1 salary

User · Answer

This is one of the popular question in any SQL interview  I am going to write down different queries to find out the nth highest value of a column   I have created a table named    Emloyee    by running the below script   CREATE TABLE Employee  Eid   float  NULL  Ename   nvarchar  255  NULL  Basic Sal   float  NULL    Now I am going to insert 8 rows into this table by running below insert statement   insert into Employee values 1  Neeraj  45000  insert into Employee values 2  Ankit  5000  insert into Employee values 3  Akshay  6000  insert into Employee values 4  Ramesh  7600  insert into Employee values 5  Vikas  4000  insert into Employee values 7  Neha  8500  insert into Employee values 8  Shivika  4500  insert into Employee values 9  Tarun  9500    Now we will find out 3rd highest Basic sal from the above table using different queries  I have run the below query in management studio and below is the result   select   from Employee order by Basic Sal desc   We can see in the above image that 3rd highest Basic Salary would be 8500  I am writing 3 different ways of doing the same  By running all three mentioned below queries we will get same result i e  8500   First Way  - Using row number function  select Ename Basic sal from              select Ename Basic Sal ROW NUMBER   over  order by Basic Sal desc  as rowid from Employee        A where rowid 2

User · Answer

Row Number    SELECT Salary EmpName FROM        SELECT Salary EmpName ROW NUMBER   OVER ORDER BY Salary  As RowNum    FROM EMPLOYEE      As A WHERE A RowNum IN  2 3    Sub Query    SELECT   FROM Employee Emp1 WHERE  N-1                     SELECT COUNT DISTINCT Emp2 Salary                  FROM Employee Emp2                WHERE Emp2 Salary  gt  Emp1 Salary                    Top Keyword    SELECT TOP 1 salary FROM         SELECT DISTINCT TOP n salary       FROM employee       ORDER BY salary DESC         a ORDER BY salary

User · Answer

NOTE  Please replace OFFSET 3 in Query with ANY Nth integer number   SELECT EmpName EmpSalary FROM SALARY ORDER BY EmpSalary DESC OFFSET 3 ROWS  FETCH NEXT 1 ROWS ONLY   Description      FETCH NEXT 1 ROWS ONLY   return only 1 row      OFFSET 3 ROWS   exclude first 3 records Here you can you any integer number

User · Answer

Select TOP 1 Salary as  3rd Highest Salary  from  SELECT DISTINCT TOP 3 Salary from Employee ORDER BY Salary DESC  a ORDER BY Salary ASC    I am showing 3rd highest salary

User · Answer

select      Min salary   from   select salary from employees order by salary desc  t where rownum lt  3    For 2nd highest salary Change 3 to 2 in above query and for Nth highest salary to N where N   1 2 3 4

User · Answer

select min salary   from  select salary        from employee        where rownum  lt  n 1        order by salary desc

User · Answer

another way to find last highest data based on date   SELECT A JID A EntryDate RefundDate Comments Refund  ActionBy FROM    select JID  Max EntryDate  AS EntryDate from refundrequested GROUP BY JID  A  Inner JOIN  SELECT JID ENTRYDATE refundDate Comments refund ActionBy from refundrequested  B  ON A JID B JID AND A EntryDate   B EntryDate

User · Answer

Method 1   SELECT TOP 1 salary FROM   SELECT TOP 3 salary   FROM employees    ORDER BY salary DESC  AS emp   ORDER BY salary ASC   Method 2     Select EmpName salary from         select EmpName salary  Row Number   over order by salary desc  as rowid            from EmpTbl     as a where rowid 3

[sql] How to find third or n?? maximum salary from salary table?

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008