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