>I am trying to find a way to optimize the following query. what I am trying to do is find the last value greater than zero inserted into table. My id is primary key. The unemp_ins_pct and id fields in payroll table are indexed. When I run this query I get a bar saying running query on screen. There has got be a quicker way to get the results I want. Here is the query:
>
>

>select unemp_ins_pct from payroll_tbl where unemp_ins_pct # 0 into array a__gen40 order by payroll_tbl.id
>	If vartype(a__gen40) # 'U' and vartype(a__gen40) # 'L'			
>		b1 = alen(a__gen40)
>		if b1 = 1
>			.q1 = (a__gen40(1,1))	&&latest unemp_ins_pct
>		else
>			.q1 = (a__gen40(b1,1))	&&latest unemp_ins_pct
>		endif
>	else
>		.q1 = 0	&&latest unemp_ins_pct not found
>	endif
>	release a__gen40
>

>
>Thank You
>Nick Patel


hi nick -

for what it's worth ... here are a couple excerpts from rushmore
help topics:

To use Rushmore

Choose one of the following options:

To access data from a single table, use a FOR clause in a command such as
AVERAGE, BROWSE, or LOCATE, or use SQL commands to update tables. For a
complete list of commands that use the FOR clause, refer to the table below.

-or-

To access data from more than one table, use the SELECT - SQL DELETE - SQL,
and UPDATE - SQL commands.

The following table lists commands that use FOR clauses. Rushmore is
designed so that its speed is proportional to the number of records
retrieved.

Understanding When Queries Are Optimized
It is important to understand when queries will be optimized and when they
will not. Visual FoxPro optimizes search conditions by looking for an exact
match between the left side of a filter expression and an index key
expression. Therefore, Rushmore can optimize an expression only if you
search against the exact expression used in an index.

For example, imagine that you've just created a table and are adding
the first index using a command such as the following:

USE CUSTOMERS
INDEX ON UPPER(cu_name) TAG name

The following command is not optimizable, because the search condition is
based on the field cu_name only, not on an expression that is indexed:

SELECT * FROM customers WHERE cu_name ="ACME"

Instead, you should create an optimizable expression using a command such
as the following, in which the expression for which you are searching
exactly matches an indexed expression:

SELECT * FROM customers WHERE UPPER(cu_name) = "ACME"

so...

it appears your original SELECT statement is not rushmore optimizable
because it only involves one table.

it appears sergey's solution is good.

be sure your search expression exactly matches the index tag definition.

be sure your search expression is on the left side of the relational
operator.