From: Tao Liang
Date: March 5 2000 7:35pm
Subject: group by conditions
List-Archive: http://lists.mysql.com/mysql/30150
Message-Id: <NDBBJNAFPIKCPBLALFKKKELMCAAA.taoliang@cdr.stanford.edu>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

Hi,

Is there a way to specify the conditions of a "GROUP BY" clause?  i.e., use
first occurrence, or use the record with the latest creation time, etc.

I am trying to make a query which left joins two tables.

SELECT t1.*, t2.*, 100*(t1.B-t2.C)/t2.C as percentage
FROM table1 AS t1 LEFT JOIN table2 AS t2
ON t1.A=t2.A
GROUP BY t1.A
ORDER BY percentage DESC
LIMIT 20

t1.A is unique.  t2.A is not unique.  So if I do the query without GROUP BY
I have duplicate A with different values.  But if I do it with GROUP BY I
only get the earliest entry of t2.A.

example,
with GROUP BY I get
ID1 X Y Z
ID2 M N O
ID3 A B C

without GROUP BY I get
ID1 I J K
ID1 E F G
ID1 X Y Z
ID2 R S T
ID2 M N O
ID3 U V W
ID3 A B C

What I want is a table with unique column A and the latest record, ordered
by some parameter (percentage)
ID1 I J K
ID2 R S T
ID3 U V W

Tao