Find the second largest record on certain criteria

[itsmeyogi]

Hi,
I would like to select the second highest paid employee record from the employee table

how do i do that

thanks in advance
yogi [sig][/sig]

 

[MichaelRed]

Create a query with the desired field (Salary) sorted in decenditg order.

In the query properties, find the "top Values" line and enter 2.

Wherever you use/need the value, open the query as a recordset and do rst.movelast and read/use the information (fields) .

(The LAST record in the query (there will only be 2) is the one you want.) [sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[Guest_imported]

Try the following query. It gives the record with second largest salary.

SELECT A.*
FROM MYTABLE A
WHERE 1 = ( SELECT COUNT( DISTINCT MYSALARY )
FROM MYTABLE
WHERE MYLALARY > A.MYSALARY )


Cheers,
Sree.
[sig][/sig]