Win a copy of Kotlin in Action this week in the Kotlin forum!
programming forums Java Java JSRs Mobile Certification Databases Caching Books Engineering Languages Frameworks Products This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
Sheriffs:
Saloon Keepers:
Bartenders:

# second highest value

deepak carter
Ranch Hand
Posts: 165
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
Posts: 21090
85
Which database system are you using?

Steve Fahlbusch
Bartender
Posts: 612
7
Find highest.... Delete that record (or make salary negative)...find highest. <- there you are

Bear Bibeault
Author and ninkuma
Marshal
Posts: 66203
151
I would assume that a non-desctructive means is required.

Bear Bibeault
Author and ninkuma
Marshal
Posts: 66203
151
• 1
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
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
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.

 With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.