List:General Discussion« Previous MessageNext Message »
From:Johan Höök Date:November 4 2005 10:10am
Subject:Re: Help with an SQL query
View as plain text  
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.
> 
> 

Thread
Help with an SQL queryGobi4 Nov
  • Re: Help with an SQL queryArno Coetzee4 Nov
    • Re: Help with an SQL queryGobi4 Nov
      • Re: Help with an SQL queryGobi4 Nov
        • Re: Help with an SQL queryJohan Höök4 Nov
          • Re: Help with an SQL queryGobi4 Nov
          • Re: Help with an SQL queryGobi4 Nov
  • Re: Help with an SQL queryRhino5 Nov
    • Re: Help with an SQL queryGobi6 Nov
Re: Help with an SQL queryJohan Höök4 Nov
  • Re: Help with an SQL queryGobi4 Nov
    • How to select on passwords?Gobi4 Nov
      • Re: How to select on passwords?Gobi4 Nov
Re: Help with an SQL queryGobi5 Nov