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
