Group BY: what's wrong with this?

[michael geary]

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

Hi Frank,

Yes, I agree with your point. However, it still leaves me without a 
fast workaround. :c)

In this particular situation, I've got an "authors" table with 
redundant rows. I intended to use the email field as a mechanism to 
identify unique individuals, so in this situation, the MySQL behavior 
of returning the first of the aggregated rows would suit me just fine.

So, can anyone propose an SQL statement that gives me the first row of 
each group of rows with a common column value? Otherwise I've either 
got to munge through my data (which is bogus anyway), or do it in 
Lingo.

Thanks,

michael geary