sql query

[Ayub ali khan]

Could any one help me how to write a query to find the second largest salary and second smallest salary from a salary table?

Thanks in advance!!

[Jussi Taimiaho]

Two approaches come to my mind. First is to order by salaray and take the next row. Limit the number of rows output, database permitting. Second using subquery, which definately is not the most efficient way, but allows this to be done with a single query.

2nd:

SELECT MAX(SALARY) FROM SALARIES WHERE SALARY <> ( SELECT MAX(SALARY) FROM SALARIES) 

[Ayub ali khan]

Thanks Jussi!! Could you metion the query for the first approach?

[Jeanne Boyarsky]

The first one:

select salary
from table
order by salary desc
<database specific clause to get first X rows>  

[Ayub ali khan]

Thank you v.Much Jeanne!! please pardon my ignorance.

From your answer I understood that you sorted the table. What would be the query to retrieve the second largest sal from the sorted table?

[Jeanne Boyarsky]

Ayub,
That is correct I sorted the table. The SQL query is what I provided in the above post.

To get the second row:

rs.next(); // get first row (and do nothing with it)
rs.next(); // get second row
System.out.prinlnt(rs.getDouble(1));  // get second largest

[Ayub ali khan]

Thank you Jeanne!! I understood the logic. Your help is very much appreciated