Arno Coetzee wrote:
> Gobi wrote:
>
>> Not sure if this is the right place to ask. I have a table, Weight,
>> with the following test data:
>>
>> idx VBS_ID Date Weight
>> 1 111111 10/3/2005 11.5
>> 2 111111 9/5/2004 10
>> 3 111111 10/7/2005 11.51
>> 4 111111 10/8/2005 11.52
>> 5 111112 10/8/2005 10.5
>> 6 111112 10/1/2005 10.3
>> 7 111112 9/28/2005 10
>>
>> What I would like to do is to get the most recent weight for each
>> unique VBS_ID. So, in this case, I want a result of:
>>
>> 111111, 10/8/2005, 11.52
>> 111112, 10/8/2005, 10.5
>>
>> Can anyone tell me how to do this? Thanks.
>>
> select VBS_ID , max(Date)
> from weight
>
Actually, I tried that. For one thing, using max() also requires a
GROUP BY clause. Otherwise, mysql complains. So I am forced to run the
following statement:
Select VBS_ID, max(Date) from Weight Group By VBS_ID
And it returns:
VBS_ID max(Date) weight
111111 10/8/2005 11.5
111112 10/8/2005 10.5
At first, I thought I had it right but when I look more closely, the
weights are incorrect. The weight for 111111 on 10/8/2005 should be
11.52. It looks look it is just returning the weight for 10/3/2005.
