General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
>*snip*
>>This statement got me 6 records back for my 6 cities I tested with. (no matter how many entries per day, and even if the high-temp was duplicated.)
>>
>>SELECT City.*, Temperature.ddate, MAX( Temperature.nhightemp);
>> FROM keycust!city INNER JOIN keycust!temperature ;
>> ON City.icityid = Temperature.icityid;
>> GROUP BY City.ccityname;
>> ORDER BY City.ccityname
>
>Because the Date is not part of the GROUP BY clause, VFP will return the date located in the last reocrd physically in the table with the same city. With your test records, put the max temp in the first record and not in one of the later records. You should get the wroong date returned.
I played with that, and you're wrong (at least in my dimension!)
My first query had "Temperature.nhightemp" ALSO in the select, and yes, it showed the last record, like you mentioned. But the MAX(Temperature.nhightemp) was indeed the highest. Infact, that first query had both values side by side in the results, and it looked like:
Temperature.nhightemp = (last record of that group)
Max(Temperature.nhightemp = (highest temp)
Dustin
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only