Hi Gobi,
the problem with your original query is that there is no
guarantee that your max(date) and it's associated VBS_ID
is picked, so what you have to ensure is that they get picked
together, so I think your statement should be like this:
SELECT VBS_ID,
SUBSTRING( MAX( CONCAT( LPAD( Date,10,'0'),weight, 11 ) AS D
LEFT( MAX( CONCAT( LPAD(Date,10,'0'),weight)), 10 ) AS W
GROUP BY VBS_ID;
What happens here is that you're not doing max on date, you're
doing it on the combination of date and weight. What might be a
problem though is your dateformat, you might run into problems
with that I guess. Also you might have to play with lengths a bit.
/Johan
Gobi wrote:
> Johan Höök wrote:
>
>> Hi Gobi,
>> there was a similar posting in august.
>> See:
>> http://lists.mysql.com/mysql/187436
>> which I think describes what you want.
>> I'll include a bit of it here as well
>> ----------
>>
>> This is out of the MySQL class and is called the Max-Concat trick.
>> ________________________________________________________________________
>> ____
>> What is the biggest country in each continent?
>> "The Rows Holding the Group-wise Maximum of a Certain Field"
>>
>> MySQL> Select Continent,
>> -> SUBSTRING(MAX(CONCAT(LPAD(Population,10,'0'),Name)),11) AS
>> Country,
>> -> 0+LEFT(MAX(CONCAT(LPAD(Population,10,'0'),Name)),10) AS
>> Population
>> -> From Country
>> -> Group By Continent
>> -> Order By Population DESC;
>> -------------
>>
>> /Johan
>
>
>
> Sorry for being slow but this statement is quite confusing and I am
> having difficulty trying to understand it in order to adapt it to my
> table. Can you explain it? Thanks.
>
>
