Get the third highest salary using 3 different approaches

Question

There are multiple methods to get the 3rd maximum salary.

Whenever an interviewer asks you to write a query of this nature, it is bound to be followed up by questions like ‘Well, this is good but can you think of some other way to do the same’

Go through the answers in comment

in progress 1
TheDataMonk 55 years 10 Answers 1563 views Grand Master 0

Answers ( 10 )

  1. RitikSharma23
    4
    Reply

    ## Three ways to get the 3rd Highest Salary
    ## Lets say table name is Emp

    ## Method-01 it will only work in MySQL Server
    Select salary
    From Emp
    Order by salary DESC
    LIMIT 2,1
    ## here LIMIT 2,1 means LIMIT [offset,] row_count

    ##Method -02 Using Top Keyword
    Select Top 1
    From (
    Select Distinct Top 3 salary
    From Emp
    Order By salary
    )As temp
    Order By salary

    ## Method-03
    SELECT salary
    FROM Emp Emp1
    WHERE ( 3) = (
    SELECT COUNT( DISTINCT ( Emp2.salary ) )
    FROM Emp Emp2
    WHERE Emp2.salary >= Emp1.salary
    )

  2. Jatin
    0
    Reply

    Using Top Approach:
    SELECT TOP 1 salary
    FROM
    ( SELECT DISTINCT TOP 3 salary FROM Table ORDER BY salary DESC ) AS temp ORDER BY salary

    Using subquery:
    SELECT salary FROM Table T1 WHERE 3-1 = (SELECT COUNT(DISTINCT salary) FROM Table T2 WHERE T1.salary > T2.salary)

    Using row number:
    SELECT salary FROM ( SELECT Salary, ROW_NUMBER() OVER (ORDER BY salary DESC) row_no FROM Table T ) WHERE row_no = 3;

  3. Medha
    1
    Reply

    Method 1- Using Dense rank in Window Function

    SELECT salary
    FROM (SELECT *, DENSE_RANK() OVER (ORDER BY salary DESC) FROM public.sales) AS r
    WHERE DENSE_RANK = 3;

    Method 2- Using Subquery
    SELECT *
    FROM(SELECT * FROM Employees ORDER BY salary DESC LIMIT 3) AS t
    ORDER BY salary
    LIMIT 1;

    Method 3- Without Window function and subquery
    SELECT *
    FROM public.sales
    ORDER BY sale_amount DESC
    OFFSET 2
    LIMIT 1;
    The OFFSET clause skips the offset rows before beginning to return the rows

  4. Medha
    0
    Reply

    Method 1- Using Dense rank in Window Function

    SELECT salary
    FROM (SELECT *, DENSE_RANK() OVER (ORDER BY salary DESC) FROM public.sales) AS r
    WHERE DENSE_RANK = 3;

    Method 2- Using Subquery
    SELECT *
    FROM(SELECT * FROM Employees ORDER BY salary DESC LIMIT 3) AS t
    ORDER BY salary
    LIMIT 1;

    Method 3- Without Window function and subquery
    SELECT *
    FROM Employees
    ORDER BY salary DESC
    OFFSET 2
    LIMIT 1;
    The OFFSET clause skips the offset rows before beginning to return the rows

  5. Medha
    1
    Reply

    Method 1- Using Dense rank in Window Function

    SELECT salary
    FROM (SELECT *, DENSE_RANK() OVER (ORDER BY salary DESC) FROM Employees) AS r
    WHERE DENSE_RANK = 3;

    Method 2- Using Subquery
    SELECT *
    FROM(SELECT * FROM Employees ORDER BY salary DESC LIMIT 3) AS t
    ORDER BY salary
    LIMIT 1;

    Method 3- Without Window function and subquery
    SELECT *
    FROM Employees
    ORDER BY salary DESC
    OFFSET 2
    LIMIT 1;
    The OFFSET clause skips the offset rows before beginning to return the rows

  6. apmuralidhar
    0
    Reply

    SELECT salary
    FROM (Select row_number() OVER (ORDER BY salary DESC) as RN,salary
    from emp_data
    WHERE salary is not null
    order by salary DESC) as emp_data_sub
    WHERE RN=3

  7. Raahul
    0
    Reply

    Using subquery –

    select salary from employee
    where salary in (select salary from employee order by salary desc limit 3)
    order by salary asc limit 1

    No subquery –

    select salary from employee
    order by salary desc
    offset 2 limit 1

  8. khushnuma
    0
    Reply

    SELECT salary
    FROM (SELECT *, DENSE_RANK() OVER (ORDER BY salary DESC) FROM Employees) AS r
    WHERE DENSE_RANK = 3;

    SELECT *
    FROM(SELECT * FROM Employees ORDER BY salary DESC LIMIT 3) AS t
    ORDER BY salary
    LIMIT 1;

    SELECT *
    FROM Employees
    ORDER BY salary DESC
    OFFSET 2
    LIMIT 1;
    The OFFSET clause skips the offset rows before beginning to return the rows

  9. rashilhila
    0
    Reply

    Method 1:
    SELECT *
    FROM EMPLOYEE_SALES
    ORDER BY SALARY DESC
    OFFSET 2
    LIMIT 1

    Method 2: Using windows function

    SELECT SALARY
    FROM
    (SELECT SALARY, DENSE RANK() OVER (ORDER BY SALARY DESC) AS DR
    FROM EMPLOYEE_SALES) T
    WHERE DR=3

    METHOD 3: SUBQUERY

    SELECT SALARY
    FROM
    (SELECT SALARY
    FROM EMPLOYEE_SALES
    ORDER BY SALARY DESC
    LIMIT 3) T
    ORDER BY SALARY
    LIMIT 1

  10. paramarthasengupta
    0
    Reply

    I have 3 approaches to solve this problem-
    Assuming The following Structure
    Table Name-SALARY_DATA

    1. Using the RANK() or DENSE_RANK() function

    SELECT SALARY
    FROM (SELECT SALARY, RANK() OVER (ORDER BY SALARY DESC) AS RNK
    FROM SALARY_DATA)AS A
    WHERE RNK=3;

    2. Using limits-
    SELECT SALARY
    FROM(SELECT SALARY
    FROM SALARY_DATA
    ORDER BY SALARY DESC
    LIMIT 3) AS A
    ORDER BY SALARY ASC
    LIMIT 1;

    3. Using Top function
    SELECT TOP 1 salary
    FROM( SELECT DISTINCT TOP 3 salary FROM SALARY_DATA ORDER BY salary DESC ) AS temp ORDER BY salary

Leave an answer

Browse
Browse