Welcome Guest. Sign in or Signup

PeopleSoft Interview Questions And Answers

Interview » Technical » SQL » SQL to find Second Largest Value

Asked by: Interviewer 2449 views SQL

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

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

Mark Kapras on Mar 18, 2016 Reply

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

I'm Existing User New User? Register Now

User ID * Password *

User ID *

Email *

Password *

Δ

## Related Questions