Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Find the second largest record on certain criteria

Status
Not open for further replies.

itsmeyogi

Programmer
Sep 18, 2000
6
IN
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]
 
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]
 
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]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top