> SELECT
> a,
> (SELECT MIN(b) FROM T1 WHERE a=t.a) AS B,
> (SELECT MIN(c) FROM T1 WHERE a=t.a) AS C
> FROM t1 t
> GROUP BY a
Why not
select a, min(b) as b, min(c) as c from t1 group by a;
?
2018-06-30 15:12 GMT+02:00, Luuk <[email protected]>:
>
> On 30-6-2018 14:55, Keith Medcalf wrote:
>> Note that this is SQLite3 specific (and specific to Sybase of the era
>> where Microsoft SQL Server was actually just a rebranded Sybase, and
>> Microsoft re-writes of SQL Server up to about 2000). Technically you
>> cannot do a query of the form:
>>
>> SELECT c1, c2
>> FROM t1
>> GROUP BY c2;
>>
>> because each column in the select list must be either an aggregate or
>> listed in the GROUP BY clause. SQLite3 allows c1 to be a bare column
>> however and the value returned is taken from "some random row" of the
>> group. If there are multiple such columns, they all come from the same
>> row in the group. Although documented as a "random" row of the group, it
>> is the first (or last) row visited in the group while solving the query
>> (and this is of course subject to change but within the same version of
>> SQLite3 will deterministically be the row either first or last in the
>> visitation order -- the actual row may of course change depending on use
>> of indexes, etc). You can re-write this part so it will work in other SQL
>> dialects that strictly enforce the requirement for c1 to be either an
>> aggregate or listed in the group by clause.
>>
>> ---
>> The fact that there's a Highway to Hell but only a Stairway to Heaven says
>> a lot about anticipated traffic volume.
>>
> Ok ,my highway to hell start here (regargind the use of SQL)
>
> In SQLite3 you are allowed to do this:
> SELECT a,b,c
> FROM t1
> GROUP BY a
>
> The values of 'b' and 'c' will be taken from a 'random' row...
>
> But if we rewrite this in SQL, i am getting something like this:
> SELECT
> a,
> (SELECT MIN(b) FROM T1 WHERE a=t.a) AS B,
> (SELECT MIN(c) FROM T1 WHERE a=t.a) AS C
> FROM t1 t
> GROUP BY a
>
> QUESTION: How does one get the proper relationship between 'B' and 'C'?,
> i mean how can one be use that both values are from the same row?
> This is not a problem to SQLite, because in SQLite the values of b and c
> seems to be originating from the same row, but what about *SQL* (if that
> exists...?)
>
> --
> some test results:
> sqlite> insert into t1 values (1,1,2);
> sqlite> insert into t1 values (1,2,1);
> sqlite> insert into t1 values (2,2,1);
> sqlite> insert into t1 values (2,1,2);
> sqlite> select a,b,c from t1 group by a;
> 1|2|1
> 2|1|2
> sqlite> SELECT
> ...> a,
> ...> (SELECT MIN(b) FROM T1 WHERE a=t.a) AS B,
> ...> (SELECT MIN(c) FROM T1 WHERE a=t.a) AS C
> ...> FROM t1 t
> ...> GROUP BY a;
> 1|1|1
> 2|1|1
> sqlite>
>
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
