On Feb 18, 2009, at 1:27 AM, johnny depp (really!) wrote:
>
> I didn't run it yet, the idea of using min(col1) = max(col1) was all I
> needed.
> I assumed it was incorrect because I thought
> referring to an ungrouped column in a group by was incorrect,
> because the
> ungrouped col would represent a set, and not a value...
>
> I just ran this:
>
> select c2 from
> (select 1 as c1, 1 as c2
> union
> select 1 as c1, 2 as c2
> union
> select 1 as c1, 3 as c2)
> group by c1
>
> and it returns 3.
>
> I think this is incorrect behaviour and should not compile because
> the result of c2 is clearly {1, 2, 3}...or am I wrong about this?
> Is this standard SQL languages behaviour?
You are correct, according to SQL this is invalid. But SQLite and other
database engines allow it because it is convenient. In such a situation
SQLite selects a value arbitrarily from the set to return.
> Igor Tandetnik wrote:
>>
>> "johnny depp (really!)"
>> <nick_reyntj...@hotmail.com> wrote in
>> message news:22057169.p...@talk.nabble.com
>>> You probably meant:
>>>
>>> select col1, case when min(col2) = max(col2) then min(col2) else
>>> 'not
>>> the same' end
>>> from mytable group by col1;
>>
>> It works for me as originally written. Do you get any errors?
>>
>> Igor Tandetnik
>>
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>
> --
> View this message in context:
> http://www.nabble.com/Little-SQL-help-please..-tp22052925p22063324.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
