After re-thinking: you're probably right and my code seems to be wrong...

>>You are right, was a mistake becuase the date is not correct.
>>Check this code:
>>
>>Select city,date,temp from mytable;
>> where temp in;
>> (select Max(temp) from mytable group by city);
>> order by city
>>
>>Juan C.
>
>
>Juan,
>
>This code would return the hottest cities and their dates, if we have multiple cities with the same max tempreture, otherwise it would return only one record.
>If you would like to have max temp for each city, the code should be this:
>

>select city, date, temp, city+str(temp,5,1) as citytemp from myTable;
>       where citytemp exists ;
>       (select city+str(temp,5,1) as citytemp, city, max(temp) as temp ;
>        from myTable group by city);
>       order by 1

 If it's not broken, fix it until it is.


My Blog