• 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: 2584
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
Pie
Posts: 20724
30
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
Pie
Posts: 3751
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