aspose 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 Spring in Action this week in the Spring 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: 435
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: 2503
    
    8

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
youtube channel
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18658
    
    8

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: 3610
    
  60

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?