Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Agile forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

retrieve nth salary thru optimized sql query

 
kri shan
Ranch Hand
Posts: 1460
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
EMP table has colum names name and salary. I would lile to retrive nth salary. How can i retrieve nth salary thru optimized sql query ?
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

I would lile to retrive nth salary

Define this. Do you mean the record in record N in the table, regardless of ordering?
 
kri shan
Ranch Hand
Posts: 1460
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
For example, i would like to retrieve 3rd maximum salary. How can i do thru sql query ?
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well in Oracle you could do something like this:


Of course ROWNUM is Oracle specific. I suppose you could rewrite the query for other platforms as required. But an easier way is to just query for everything and use the ResultSets absolute(int row) method. Or iterate through the ResultSet and break at row 3. So long as you have ordered the query, and there are 3 or more records in that table it should work.
[ July 28, 2004: Message edited by: Paul Sturrock ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic