On Mar 31, 2005, at 3:39 PM, michael geary wrote:
> using V4MD:
>
> sql = select authors.* from authors GROUP BY authors.email ORDER BY
> authors.lastName asc
>
> results in the error:
> "In the SELECT clause you can use only fields listed in the GROUP BY
> and aggregative functions."
>
> In MySQL this query works just fine. What am I doing wrong in
> Valentina?
While MySQL does return a result, it is hardly valid. You are asking
for one value for each group for each field when there can be many due
to the GROUP BY. Which one to return? MySQL seems to return the first
value, but I fail to see how that means anything. In fact, I think it
is an error in MySQL.
When you use GROUP BY, you must only select the GROUP BY fields.
select authors.lastName from authors GROUP BY authors.email ORDER BY
authors.lastName asc
You can select aggregate functions too.
select authors.lastName, sum(amount), count(*) from authors GROUP BY
authors.email ORDER BY authors.lastName asc
After all, that is the purpose of GROUP BY. If you wanted to select all
fields, then surely you must want all values and hence why are you
using GROUP BY in the first place?
HTH,
Frank Schima
_______________________________________________
Valentina mailing list
Valentina /at/ lists .D.O.T macserve.net
http://lists.macserve.net/mailman/listinfo/valentina
©2005 Frank Schima |
