File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Oracle/OAS and the fly likes How to find the second maximum salary of an employee using query? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Head First Android this week in the Android forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "How to find the second maximum salary of an employee using query?" Watch "How to find the second maximum salary of an employee using query?" New topic
Author

How to find the second maximum salary of an employee using query?

Padma priya Gururajan
Ranch Hand

Joined: Oct 05, 2006
Posts: 437
Hi,

How can I find the second maximum salary of an employee using joins or query?

Thanks,
Padma priya N.G.


Padma priya N.G.
Be the change you want to be - Mahatma Gandhi
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2541
    
  10

First find a query to find the maximum salary. Then create a query that finds the biggest salary that is smaller than the first one.

Or

Create a query that returns the salaries ordered by amount descending (highest amount comes first in the result). Then loop until you find the first salary that is smaller than the one in the first record.


OCUP UML fundamental and ITIL foundation
Paul Clapham
Sheriff

Joined: Oct 14, 2005
Posts: 19693
    
  10

Don't forget to account for situations where two (or more) employees have the same amount and it's the maximum, and where two (or more) employees have the same amount and it's the second-largest.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3717
    
  61

Analytic functions can be used here, namely RANK or DENSE_RANK. It doesn't spare you of thinking about Paul's remarks, though.

See also the explanation of RANK and DENSE_RANK on AskTom.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to find the second maximum salary of an employee using query?
 
jQuery in Action, 3rd edition