Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
JavaRanch.com/granny.jsp
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 Murach's Java Servlets and JSP this week in the Servlets 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: 434
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: 2491
    
    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: 18541
    
    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: 3606
    
  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.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: How to find the second maximum salary of an employee using query?
 
Similar Threads
Query -- select maximum salary in a sum table
sql query
Display the sequence numbers on the output rows of a select in Oracle 10g
database
SQL Query