Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Padma priya Gururajan
Ranch Hand
Posts: 437
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

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

Thanks,
Padma priya N.G.
 
Jan Cumps
Bartender
Posts: 2588
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Paul Clapham
Sheriff
Posts: 21107
32
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic