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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
