Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • How to select second highest salary from a table?

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 396
    Comment on it

    This question is asked a lot in interviews, so here some of the methods by which one can get the second highest salary of an employee from a table.

    Suppose the Employee is like this

    ID Name Salary
    Table: Employee
    1 Pankaj Singh Chauhan 150000
    2 Shiristi Nawani 10000
    3 Shilpi badoni 25000
    4 Neha Bhatt 100000
    5 Suresh Ramola 200000

    Method 1:

    SELECT MAX(salary) FROM Employee WHERE salary < (SELECT MAX(salary) FROM Employee)

    For example in the above table Employee the R.H.S. select statement will return the maximum salary i.e, 200000 and the L.H.S. select statement first will return 200000 but the condition will become false and it will select new value i.e, 150000 as it is < 200000, which is second highest salary.

    Method 2:

    SELECT MAX(salary) FROM Employee WHERE salary NOT IN(SELECT MAX(salary) FROM Employee)

    Same method applied here, the only difference is that the first select will match whether the selected value i.e, 150000 lies in the resultant value i.e., 200000 from the another select statement.

    Method 3:

    SELECT MAX(salary) FROM Employee WHERE salary!= (SELECT MAX(salary) FROM Employee)

    Same method applied here, the only difference is that the first select will match whether the selected value i.e, 150000 not equal to resultant value i.e., 200000 from the another select statement.

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: