Welcome Guest. Sign in or Signup

3 Answers

SQL to find Second Largest Value

Asked by: 2380 views SQL

Can you write an SQL that selects the second largest value from a table? Assume appropriate table structure.

Related Questions

3 Answers



  1. Janardhan on Mar 02, 2013 Reply

    select max(sal) from ps_emp where sal<max(sal);

    -4 Votes Thumb up 0 Votes Thumb down 0 Votes



    • lilsam on Jul 14, 2016 Reply

      Consider a scenario where we want to find the second highest paid employee’s salary from the EMPLOYEE record.

      In order to find that, consider the below data present in EMPLOYEE record:
      EMPLID SALARY
      emp01 10000
      emp02 5000
      emp03 25000
      emp04 8000

      As per the above data, we know that the second highest paid employee is “emp01” and the value returned should be 10000. Below query will return the following:

      SELECT MAX(SALARY)
      FROM EMPLOYEE
      WHERE SALARY NOT IN (SELECT MAX(A.SALARY) FROM EMPLOYEE A)

      The above query will first find out the maximum salary from the employee record i.e 25000 and then when it executes the WHERE clause(in this the SALARY field checks each value from its set of data i.e 10000,5000,25000,8000) and compares it with the MAX value returned from the subquery where it finds 25000 so now it excludes 25000 from its list because of the NOT IN clause.
      Now, we are left with just 10000,5000 and 8000 out of which the MAX is 10000,which is selected by the MAIN query.

      0 Votes Thumb up 0 Votes Thumb down 0 Votes



  2. Mark Kapras on Mar 18, 2016 Reply

    select max(sal) from ps_emp where sal<(select max(sal) from ps_emp );

    0 Votes Thumb up 0 Votes Thumb down 0 Votes


Answer Question