File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Returning top 10 Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of REST with Spring (video course) this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Returning top 10" Watch "Returning top 10" New topic

Returning top 10

Svend Rost
Ranch Hand

Joined: Oct 23, 2002
Posts: 904

Motivation example:
I have a table containing "persons". A person tuble is (id,name,value).
I need to return the 10 riches (as in, "most valueable") persons. Atm. im
using the following query:

Then I use a for(i=1;i<=10;i++) loop. I get the wanted result, but my problem is
that this will be pretty ineffective if I have 100000000 records in my DB.

Does anyone have any ideas or suggestions?

Please note, that I do not have any other tables in my DB. I thought about
making a table "rank" containing a "rank" and the value.. the problem is,
that with this solution I have to sort the table everytime I update a persons

Edit: The persons value will be updated more often, than one wants to get
the top 10.

Thanks in advance.

/Svend Rost
[ May 02, 2005: Message edited by: Svend Rost ]
Svend Rost
Ranch Hand

Joined: Oct 23, 2002
Posts: 904

to those who might be interested you can get the top N records with the
following SQL queries:

The above works for Access and MS SQL Server


works for MySQL.

/Svend Rost
steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 862
In sybase ASE and ASA you can do the following: - a fast, free open source performance tuning api.
JavaRanch Performance FAQ
Consider Paul's rocket mass heater.
subject: Returning top 10
It's not a secret anymore!