Re: Help with this SQL statement please...

On Mon, 16 May 2005 09:27:47 GMT, "harry" <spammemothers_at_yahoo.co.uk> wrote:

>I have a table called milestone containing this data -
>
>mile_id actual_date
>------- -----------
>3 12/02/2003
>1 10/10/2002
>2 31/01/2003
>4
>5
>6
>
>I want a SQL statement to return ONLY the row with the highest mile_id AND
>with an actual_date. This works -
>
> SELECT MAX(mi.mile_id), MAX(mi.actual_date) FROM milestone mi WHERE AND
>mi.actual_date IS NOT NULL
>
>returning
>
> 3 12/02/2003
>
>
>which is correct but is this the best way? - the bit I'm really querying is
>the "MAX(mi.actual_date)"
>
>many thanks
>
>harry
>

Not correct. Instead as simple as :

select * from milestone where actual_date =(select max(actual_date) from milestone);

With no indexes on milestone, this statement requires a double full table scan. Consider creating an index on actual_date or even actual_date+mile_id (in that order).

• Kenneth Koenraadt