how to select the top x number of records

Judy Deo
Judy Deo used Ask the Experts™
on
if i have a field name called points in a table named blah, how do i write the query so that
it will select the top 40 records that have the most points, putting them in the correct order from highest to least?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
<cfquery name="blah" datasource="somewhere" maxrows=40>
Select *, points
from table
ORDER BY points DESC
</cfquery>
This should work for what you are trying to do
Select top 40 points from points_table order by points DESC

Hope this helps.
Commented:
in ORACLE i wld use

select points from (Select points from points_table order by points DESC) where rownum <= 40

K'Rgds
Anand
Commented:
And for microsoft access:

<cfquery name="QUERYNAME" datasource="#DATASOURCE#">
         SELECT points
         FROM points_table
         ORDER BY points DESC
         LIMIT 40
</cfquery>

Mause
in Ms-Access or Ms-SQL you can write the query as

SELECT top 40 * FROM points_table order by points table;


K'Regards

Jayesh

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial