If you want the row with the minimum B, and the row with a minimum C, then the union of two queries would seem to be appropriate.
Gerry Snyder On Sat, Jun 30, 2018, 6:45 AM Luuk <luu...@gmail.com> wrote: > > > 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
