Re: Attribute must be GROUPed.... ?

David Walker wrote:

>I use min(fieldname) as fieldname which is a little more than I want to type
>but doesn't disturb my groupings.
>
Min( ) and max( ) somehow seem to also reduce to a limit 1 on duplicate
values of which I'm not sure is of much use in the discussion. But if
the groups are large then your doing O(n) passes over each column/field.

I've seen some databases do the equivalent of a unique on the columns
(*A) and where there were different values break the grouping which IMHO
is completely wrong. Others have only displayed the first match and
discarded the rest for the group on a non-aggregated column/field. (*B).

TABLEA
a | b |c
---+---+---
1 | 1 | 3
1 | 2 | 3
3 | 3 | 3
2 | 2 | 3

select a, b from TABLEA group by c would produce

Behavior A
a | b
---+---
1 | 3
2 | 3
3 | 3

OR

Behavior B
a | b
---|---
1 | 3

If I had to pick a behavior I would have picked behavior B because one
could infer that the nongrouped value was unimportant or if you needed
an aggregate operation it would have been specified. Another option
would be to be completely indiscriminate and go with the first tuple it
found and skip to next token/match/group (*C). Even C* would be ok
IMO, it might even be faster.

Behavior C1 |
a | b |
---|--- |
1 | 3 |
|
Behavior C2 |
a | b |
---|--- +----- All are possible outcomes...
3 | 3 |
|
Behavior C3 |
a | b |
---|--- |
2 | 3 |

>
>On Wednesday 30 April 2003 05:02 pm, Daniele Orlandi wrote:
>
>
>>Stephan Szabo wrote:
>>
>>
>>>AFAIK it's a requirement of the SQL spec. (SQL92(draft) 7.9 SR 7, "each
>>><column reference> in each <value expression> that references a column
>>>of T shall reference a grouping column or be specified within a <set
>>>function specification>."
>>>
>>>
>>I see... How should the "shall" term be considered ? I don't have much
>>knowledge of the SQL specs language.
>>
>>How other DBMS behave in this case ? I know that mysql doesn't enforce
>>this requirement but... mysql is not a perfect reference wrt standards
>>compliance.
>>
>>
>>
>>>Well, it'd mean you didn't have to put the extra columns in the group by
>>>list to make them grouping columns.
>>>
>>>
>>This is what I currently do as a workaround, but it's not much clean
>>expecially when you have many ungrouped fields in the target list.
>>
>>Bye!
>>
>>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>