Tao Liang writes:
> 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
Hi!
No, there is no way to get it with SQL. There is simply no way to
specify instances of other columns in GROUP BY non-unique column.
At least, not possible in SQL to my knowledge.
This is possible in Quel language, which does not exist anymore.
Regards,
Sinisa
+----------------------------------------------------------------------+
| TcX ____ __ _____ _____ ___ == mysql@stripped |
| /*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic |
| /*/ /*/ /*/ \*\_ |*| |*||*| mailto:sinisa@stripped |
| /*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaka, Cyprus |
| /*/ /*/ /*/\*\_/*/ \*\_/*/ |*|____ |
| ^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^ |
| /*/ \*\ Developers Team |
+----------------------------------------------------------------------+
