From: <sinisa
Date: March 6 2000 2:36pm
Subject: Re: group by conditions
List-Archive: http://lists.mysql.com/mysql/30207
Message-Id: <14531.49796.931986.562096@localhost.localdomain>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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       |
+----------------------------------------------------------------------+