Re: [sqlite] Min() ; was: Re: if exist

2010-03-09 Thread Tim Romano
On 3/9/2010 2:13 PM, P Kishor wrote: <> about the following example I provided: >> 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. >> Foo was simply my shorthand for "another

Re: [sqlite] Min() ; was: Re: if exist

2010-03-09 Thread Scott Hess
On Tue, Mar 9, 2010 at 10:46 AM, Tim Romano wrote: > On 3/9/2010 10:56 AM, Scott Hess wrote: >> On Tue, Mar 9, 2010 at 7:15 AM, Tim Romano  wrote: >>> Of these three: >>> >>> select c from T where 1=2                 // returns 0 rows >>> select min(c)

Re: [sqlite] Min() ; was: Re: if exist

2010-03-09 Thread P Kishor
On Tue, Mar 9, 2010 at 12:46 PM, Tim Romano wrote: > On 3/9/2010 10:56 AM, Scott Hess wrote: >> On Tue, Mar 9, 2010 at 7:15 AM, Tim Romano  wrote: >> >>> Of these three: >>> >>> select c from T where 1=2                 // returns 0 rows >>> select

Re: [sqlite] Min() ; was: Re: if exist

2010-03-09 Thread Igor Tandetnik
Tim Romano wrote: > select min(c) from T where 1=2 > > returns 1 row that contains despite the presence of the > aggregate function Not despite - _because_ of. If you didn't have the aggregate there, you'd get zero rows. > and so > >select min(c) is null from T

Re: [sqlite] Min() ; was: Re: if exist

2010-03-09 Thread Tim Romano
On 3/9/2010 10:56 AM, Scott Hess wrote: > On Tue, Mar 9, 2010 at 7:15 AM, Tim Romano 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

Re: [sqlite] Min() ; was: Re: if exist

2010-03-09 Thread Igor Tandetnik
Tim Romano 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,

Re: [sqlite] Min() ; was: Re: if exist

2010-03-09 Thread P Kishor
On Tue, Mar 9, 2010 at 9:56 AM, Scott Hess wrote: > On Tue, Mar 9, 2010 at 7:15 AM, Tim Romano 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

Re: [sqlite] Min() ; was: Re: if exist

2010-03-09 Thread Scott Hess
On Tue, Mar 9, 2010 at 7:15 AM, Tim Romano 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

Re: [sqlite] Min() ; was: Re: if exist

2010-03-09 Thread Pavel Ivanov
> 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

Re: [sqlite] Min() ; was: Re: if exist

2010-03-09 Thread P Kishor
On Tue, Mar 9, 2010 at 7:34 AM, Pavel Ivanov 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

Re: [sqlite] Min() ; was: Re: if exist

2010-03-09 Thread P Kishor
On Tue, Mar 9, 2010 at 6:18 AM, Tim Romano 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

[sqlite] Min() ; was: Re: if exist

2010-03-09 Thread Tim Romano
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 select min(c) from T where 1=2 // returns 1 row select min(88,99) from T where 1=2 // returns 0 rows Tim Romano On 3/9/2010 4:15

Re: [sqlite] Min() ; was: Re: if exist

2010-03-09 Thread Pavel Ivanov
> Wrapping a column in the min() function causes a query that returns no > rows to return a  row? Yes, it's SQL standard for aggregate functions (min, max, avg and count): without GROUP BY clause they always return one row. Pavel On Tue, Mar 9, 2010 at 7:18 AM, Tim Romano

Re: [sqlite] Min() ; was: Re: if exist

2010-03-09 Thread Tim Romano
On 3/9/2010 8:04 AM, P Kishor wrote: > >> 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