Returning top 10

[Svend Rost]

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:

"SELECT * FROM persons ORDER BY persons.value DESC;

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]

Hello,

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

"SELECT TOP 10 * FROM persons ORDER BY persons.value DESC;"

The above works for Access and MS SQL Server

and

"SELECT * FROM persons LIMIT 10"

works for MySQL.

/Svend Rost

[steve souza]

In sybase ASE and ASA you can do the following:

 
set rowcount 10 
SELECT * FROM persons ORDER BY persons.value DESC