Radovan Antloga wrote:
> select min(A)
> from TEST
> where B is null
> and A > 3;
>
> if you replace min(A) with * you get empty result set as expected
> but with min or max or avg you get one record
This is just how aggregate functions in SQL work.
When you're using GROUP BY, you get exactly one result row per group.
And if there are no rows that are grouped, there are no groups, and
therefore the result is empty.
When you're not using GROUP BY, you always get exactly one result row
out of the aggregate function(s), even if they aggregate an empty set.
If you do want an empty result if the (filtered) source table is empty,
you have to add grouping (by some dummy value):
SELECT min(a)
FROM Test
WHERE b IS NULL
AND a > 3
GROUP BY NULL;
Regards,
Clemens
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
