permaculture playing cards*
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
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: 1372
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: 1372
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