• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

second highest value

 
Ranch Hand
Posts: 165
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Sheriff
Posts: 22783
131
Eclipse IDE Spring VI Editor Chrome Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Which database system are you using?
 
Bartender
Posts: 612
7
Mac OS X Python
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Find highest.... Delete that record (or make salary negative)...find highest. <- there you are
 
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I would assume that a non-desctructive means is required.
 
Bear Bibeault
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic