Pretty sure you meant: select * from (select min(t) as t from T) as T;
adding a group by will return the minimum value of T for each group of T which is equivalent to select distinct T from T min(t) group by t, max(t) group by t, avg(t) group by t, distinct t all return the same results. > -----Original Message----- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of James K. Lowden > Sent: Monday, 15 February, 2016 13:58 > To: sqlite-users at mailinglists.sqlite.org > Subject: Re: [sqlite] Correlated subquery throwing an error > > On Mon, 15 Feb 2016 10:39:31 +0100 > Clemens Ladisch <clemens at ladisch.de> wrote: > > > > you need to explicitly limit a subquery that is a field and must > > > only ever return 1 result if the where clause is ambiguous about it > > > > Not in SQLite. (It ignores superfluous rows, and returns NULL if > > there are no rows.) > > Yes, but the right way to do is to use logic instead of brute force. > Instead of > > select * from (select t from T order by t limit 1) as T; > > use > > select * from (select min(t) as t from T group by t) as T; > > The latter has the benefit that it can be easily modified to add > COUNT(*) to the subquery, and check for perhaps erroneous cases where > COUNT(*) > 1. > > --jkl > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
