From: Ahmed Rehman
Date: June 13 2003 5:31pm
Subject: "GROUP BY" and "LEFT JOIN" within one statement
List-Archive: http://lists.mysql.com/mysqldoc/2
Message-Id: <GBECIHLLFDJHHFEOHDGFGEJECCAA.arehman@braus.ch>
MIME-Version: 1.0
Content-Type: multipart/mixed;	boundary="----=_NextPart_000_000C_01C331E2.65642E20"

------=_NextPart_000_000C_01C331E2.65642E20
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

Hi,
It is not clear what hapens when you mix "GROUP BY" and "LEFT JOIN" within a
SQL statement,

sample:
I want to find the row entries of certain types and LATEST Max or Min in
timestamp of each type,

I think such problems happen very often (think of saldos usw ...)

Theoretically I could emagine something like this to work, I join the table
with a grouped one in which I have Max values:

Select table.something, table.id,
table.timestamp as time, Max(table2.timestamp) as latest
From table
Left Join table as table2 ON table.id=table2.id
where table.type in(1,6,5,3,7,4)
Group by table2.type


later i will add HAVING latest = time

this doesn't work (take away having to see effekt:)

- first the Group by reduces amount of rows even on the left table, this
means Group by is running on the result over the join (so which obviouosly
random results for the left table did it collect while grouping?)
I think it shouldn't be allowed to group over rows of the left table (since
there I don't use Max Min or other specifiers)


you do point in the dokumentation "3.5.4 The Rows Holding the Group-wise
Maximum of a Certain Field" to using such statements,

still I feel its completely unclear what are the rules for "GROUP BY" and
"LEFT JOIN".

anyone know some rules to this?

ahmed

------=_NextPart_000_000C_01C331E2.65642E20--