On Tue, Mar 9, 2010 at 12:46 PM, Tim Romano <tim.rom...@yahoo.com> wrote: > On 3/9/2010 10:56 AM, Scott Hess wrote: >> On Tue, Mar 9, 2010 at 7:15 AM, Tim Romano<tim.rom...@yahoo.com> wrote: >> >>> Of these three: >>> >>> select c from T where 1=2 // returns 0 rows >>> select min(c) from T where 1=2 // returns 1 row >>> select min(88,99) from T where 1=2 // returns 0 rows >>> >>> the only case that "threw" me is the second one, where a row is returned >>> despite a WHERE condition that should yield an empty set (or so I thought). >>> >> The first and last cases will run for each row in the WHERE clause. >> The second case is aggregating over all c, and will always return one >> row, even if the WHERE clause selects many rows, so it is consistent >> for it to return one row if the WHERE clause selects for no rows. >> It's as if you coded it like this: >> >> SELECT min(SELECT c FROM t WHERE ...) >> >> meaning the minimum of that set of inputs, and if that set is empty, >> there is no minimum, so you get a result of NULL, but not no result, >> if you see what I mean. >> > > Thanks for the replies, Scott and Igor and Pavel and Puneet. What I see > is that an aggregate function needs to partner with the GROUP BY clause > in order for nulls to be removed from the aggregated set. > > select min(c) from T where 1=2 > > returns 1 row that contains <null> despite the presence of the aggregate > function > > and so > > select min(c) is null from T where 1 =2 > > returns true (1). But > > select min(c) from T where 1=2 > /group by/ foo > > returns no rows, presumably because the null value was removed from the > aggregated set. >
I don't know what "/group by/ foo" means. Is that something you tried and actually got no rows? If you did the following SELECT Min(c) AS foo FROM t WHERE 1=2 GROUP BY foo; you will get an error: SQL error: aggregate functions are not allowed in the GROUP BY clause If you did the following SELECT Min(c) AS c FROM t WHERE 1=2 GROUP BY foo; You will get an error: SQL error: no such column: foo Here is the rule -- if you are SELECT aggregate and non aggregate columns, then you should use GROUP BY for the non-aggregate columns. Assuming a table t(c, foo); the following is valid SELECT Min(c) AS minc, foo FROM t WHERE 1=2 GROUP BY foo; Min(), Max(), Count(), Sum(), etc. are aggregate functions, and act on the returned result set. Abs(), Glob(), Hex(), etc. are non-aggregate functions. Min(x,y,z...) behaves like a non-aggregate function. -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu ----------------------------------------------------------------------- Assertions are politics; backing up assertions with evidence is science ======================================================================= _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
