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
