SQL to find Second Largest Value

3 Answers

1. 
   

   
   

   Janardhan on Mar 02, 2013 Reply

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

   -4 Votes 0 Votes  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 0 Votes  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 0 Votes  0 Votes