wood burning stoves 2.0*
The moose likes JDBC and the fly likes retrieve nth salary thru optimized sql query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "retrieve nth salary thru optimized sql query " Watch "retrieve nth salary thru optimized sql query " New topic
Author

retrieve nth salary thru optimized sql query

kri shan
Ranch Hand

Joined: Apr 08, 2004
Posts: 1371
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

Joined: Apr 14, 2004
Posts: 10336


I would lile to retrive nth salary

Define this. Do you mean the record in record N in the table, regardless of ordering?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
kri shan
Ranch Hand

Joined: Apr 08, 2004
Posts: 1371
For example, i would like to retrieve 3rd maximum salary. How can i do thru sql query ?
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

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 ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: retrieve nth salary thru optimized sql query
 
Similar Threads
DB2 700 and 701 - Test Sample
what is sqlj?
how to select 2nd,3rd or the Nth highest salary from a table in desc or ascen order?
Websphere Basics
jasper Reports