Re: SELECT difficulties

[Michael Stassen]

That won't work either.  You cannot use an aggregate function in the WHERE 
clause.  The purpose of the WHERE clause is to determine which rows to look 
at, but you won't know the MIN(price) until after you've looked at them. 
You can use aggregate functions in the HAVING clause, but that filters rows 
at the end, long after the random offerid has been chosen, so it won't have 
the effect you intend.

Once you use aggregate functions, you aren't really selecting rows anymore. 
 Rather, you are selecting results of the aggregate function(s) labeled by 
values from the grouped column(s).  As a convenience, mysql allows you to 
use other column values to label your groups, but that's all they are - 
labels.  If the other column has different values within groups, however, 
this won't be useful.

From the manual:
"MySQL extends the use of GROUP BY so that you can use columns or 
calculations in the SELECT list that don't appear in the GROUP BY clause. 
This stands for any possible value for this group... Do not use this feature 
if the columns you omit from the GROUP BY part are not unique in the group! 
You will get unpredictable results."
<http://dev.mysql.com/doc/mysql/en/GROUP-BY-hidden-fields.html>

To get the rows which match the groupwise max/min, you must use a subquery, 
a temporary table, or the max-concat trick, as described in the manual 
<http://dev.mysql.com/doc/mysql/en/example-Maximum-column-group-row.html>.

Michael
Joćo Paulo Freire wrote:
SELECT offerid, resellerid, price FROM A WHERE price = MIN(price) GROUP BY
resellerid;
----- Original Message -----
From: "Michael Stassen" <[EMAIL PROTECTED]>
To: "Daniel Lahey" <[EMAIL PROTECTED]>
Cc: "Haitao Jiang" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Friday, July 30, 2004 7:33 PM
Subject: Re: SELECT difficulties
That won't work.  For each resellerid, you'll get the minimum price and an
effectively random offerid.
Michael
Daniel Lahey wrote:

You need to use the 'GROUP BY' clause:
SELECT offerid, resellerid, MIN(price) FROM A GROUP BY resellerid;
Cheers,
Dan
On Jul 30, 2004, at 2:37 PM, Haitao Jiang wrote:

Hi, there
Maybe this question is not MySQL specific, but I just wondering if
MySQL has any way to doing this:
I have a table A like following:
offerId resellerId price
------------------------------
1        r1           5
2        r1          10
3        r2          12
4        r2           4
------------------------------
How can I select only one row from each reseller with the the lowest
price from this reseller? i.e. the result should be
1  r1  5
4  r2  4
Thanks in adavnce

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]