• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

second highest value

 
deepak carter
Ranch Hand
Posts: 165
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
how to find the second highest salry

i am using this logic....

select * from emp e where
2 =(select count(distinct sal) from emp where e.sal<=sal)

but need an alternative


Thanks in advance
 
Rob Spoor
Sheriff
Pie
Posts: 20671
65
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Which database system are you using?
 
Steve Fahlbusch
Bartender
Posts: 605
7
Mac OS X Python
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Find highest.... Delete that record (or make salary negative)...find highest. <- there you are
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 65229
95
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I would assume that a non-desctructive means is required.
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 65229
95
IntelliJ IDE Java jQuery Mac Mac OS X
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
P.S. Do you really think that sal is a better name than salary?

Why people insist on freeze-drying perfectly good words is beyond my capacity to imagine.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Bear Bibeault wrote:P.S. Do you really think that sal is a better name than salary?

Why people insist on freeze-drying perfectly good words is beyond my capacity to imagine.

Deepak is probably not guilty in this case: emp and sal are parts of the (in)famous scott/tiger Oracle sample schema.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Deepak, if you're indeed on Oracle, you might use analytic functions to do this:

Other databases may also support analytics.

However, "second highest salary" is a bit ambiguously defined. Consider salaries 5000, 5000, 4000. Which of them is second highest? One interpretation (the one I used in the above query) is that 4000 is the second highest. Another interpretation is that there is no second-highest salary, there are two maximum salaries and one third-highest salary. Depending on context, one or the other of these interpretations can be the "right" one.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic