[V4WS 1.11] Re: Retrieving field values of record found with MAX

[Ruslan Zasukhin]

On 11/16/06 11:16 AM, "Steven Ronald" <stevenrt39 at yahoo.com.au> wrote:

> If I have a table x:
> 
> foo    bar   hooh
> 0.3    3       45
> 0.2    4       87
> 0.6    3       23
> 0.5    6       33
> 
> is it possible to use a single SQL statement to find
> the value of hooh for the record with the maximum
> value of foo (ie hooh=23)?
> 
> SELECT  MAX(foo),hooh FROM x GROUP BY hooh
> 
> does not work - it returns all 4 records.

I think you have WRONG EXPECTATIONS.

Look on such data:

 foo    bar   hooh
-----------------------
 0.3    3       45
 0.2    4       87
 0.6    3       23
 0.5    6       33
 0.6    3       29


Now Exists 2 records with 0.6 MAX value.

So what answer must be for HOOH ???

    23 or 29 ?

This is why your query have no sense for SQL standard


-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]