At 08:02 PM 23/07/2004 +0000, you wrote:

>Is there a better way to write the following query?
>I want to get the cost and quantity for a product
>with the max date and time for that product.
>
>select cost, quantity, date, time
>from table
>where date =
>( select max(date)
>from table
>where product = '406423004265' AND quantity > 0 )
>AND time =
>( select max(time)
>from table
>where product = '406423004265' AND quantity > 0 )

Aliasing errors and illegal identifiers apart...you need a descending index
on the date and time columns (or a desc index on each).

Your re-entrant subqueries need aliasing and correlating:

select t1.cost, t1.quantity, t1.adate, t1.time
from table t1
where t1.adate =
( select max(t2.date) from table t2
where t2.product = t1.product and t2.quantity > 0)
AND t1.time =
( select max(t3.time) from table t3
where t3.product = t1.product and t3.quantity > 0)
where t1.product = '406423004265'

This is less complicated and might be faster (assuming you add the index):

select first 1 cost, quantity, adate, atime
from table
where product = '406423004265' AND quantity > 0
order by adate, atime

/heLen