On Tue, Mar 9, 2010 at 7:34 AM, Pavel Ivanov <paiva...@gmail.com> wrote:
>> sqlite> SELECT Min(m) AS m FROM t WHERE 1=2;
>> m
>> ----------
>> sqlite> SELECT Min(m) FROM t;
>> Min(m)
>> ----------
>> 88
>
> Puneet, note that you probably missed one empty row of terminal output
> in the first query above and when there's no row returned sqlite3
> command line utility doesn't print any headers information. Try this
> query to check things out: SELECT COUNT(*) FROM (SELECT Min(m) AS m
> FROM t WHERE 1=2).
>
>


Yes, you are absolutely correct.


> Pavel
>
> On Tue, Mar 9, 2010 at 8:04 AM, P Kishor <punk.k...@gmail.com> wrote:
>> On Tue, Mar 9, 2010 at 6:18 AM, Tim Romano <tim.rom...@yahoo.com> wrote:
>>> Wrapping a column in the min() function causes a query that returns no
>>> rows to return a  row?
>>>
>>> select c from T where 1=2                 // returns 0 rows
>>
>> The above is correct SQL, and the answer is correct.
>>
>>> select min(c) from T where 1=2         // returns 1 row
>>
>> You should be getting 0 rows; I do. What version of sqlite are you running?
>>
>> SQLite version 3.6.19
>> Enter ".help" for instructions
>> Enter SQL statements terminated with a ";"
>> sqlite> CREATE TABLE t (m);
>> sqlite> INSERT INTO t VALUES (88);
>> sqlite> INSERT INTO t VALUES (89);
>> sqlite> SELECT * FROM t;
>> m
>> ----------
>> 88
>> 89
>> sqlite> SELECT Min(m) AS m FROM t WHERE 1=2;
>> m
>> ----------
>> sqlite> SELECT Min(m) FROM t;
>> Min(m)
>> ----------
>> 88
>>
>>
>>> select min(88,99) from T where 1=2  // returns 0 rows
>>
>> The above is correct SQL and the answer is correct. Per the docs,
>> "Note that min() is a simple function when it has 2 or more arguments
>> but operates as an aggregate function if given only a single
>> argument."
>>
>> Finally, note that when returning both aggregate and non-aggregate
>> columns, you should use the GROUP BY clause. I believe that SQLite
>> will return rows even without GROUP BY, but the answer may be
>> undependable.
>>
>>>
>>> Tim Romano
>>>
>> ..
>>
>>


-- 
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

Reply via email to