jQuery in Action, 3rd edition
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
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:

http://www.jamonapi.com/ - a fast, free open source performance tuning api.
JavaRanch Performance FAQ
I agree. Here's the link: http://aspose.com/file-tools
subject: Returning top 10
It's not a secret anymore!