Gobi wrote:
> 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.
>
As a further note on my above post, I added "idx" to my query:
Select idx, VBS_ID, max(Date) from Weight Group By VBS_ID
and it returned:
idx VBS_ID max(Date) weight
1 111111 10/8/2005 11.5
5 111112 10/8/2005 10.5
So actually, it is returning the wrong rows.
