aspose file tools*
The moose likes JDBC 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 EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Returning top 10" Watch "Returning top 10" New topic
Author

Returning top 10

Svend Rost
Ranch Hand

Joined: Oct 23, 2002
Posts: 904
Hi,

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
value.

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
Hello,

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

and

works for MySQL.

/Svend Rost
steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 852
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
 
Similar Threads
What's the difference - one table, or multiple tables?
SQL Question: Getting the rank
Call for Global Taxation
SQL question: returning the posts between rank y and z
DB crashes when it's used simultaneous