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
