>>> 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; >> ? > > It still does not quarantee that the valuse show for b and c are comming > from the same row...
select a, b, min(c) as c from (select a, min(b) as b from t1 group by a) join t1 using(a, b) group by a, b; ? 2018-06-30 15:45 GMT+02:00, Luuk <luu...@gmail.com>: > > > On 30-6-2018 15:39, Abroży Nieprzełoży wrote: >>> 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; >> ? > > It still does not quarantee that the valuse show for b and c are comming > from the same row... > > >> >> 2018-06-30 15:12 GMT+02:00, Luuk <luu...@gmail.com>: >>> 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 >>> sqlite-users@mailinglists.sqlite.org >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >>> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
