Brad, Puneet,
Thanks for getting back to me so quickly. I feel like a fool ;-)
The error of "misuse of aggregate function MAX()" confused me. I ran the
same query in SQL Server, got an error message that I understood and
realised instantly that my brain wasn't switched on and that I needed to do
it exactly as shown in Puneet's example.
Lesson of the day for me is:
"If something doesn't work in Sqlite, try the same thing in SQL Server
before assuming the problem is with Sqlite as opposed to me!"
Cheers
Keith
P Kishor-3 wrote:
>
> On 12/4/08, Brad Stiles <[EMAIL PROTECTED]> wrote:
>> > SELECT *
>> > FROM MyTableWithDates
>> > WHERE datetime("now") > MAX(dtEndDate)
>>
>>
>> What is it that you are actually trying to do with this query? As
>> formulated (even if it were syntactically correct, which I don't think
>> it is), you are either going to get every row in the table, or no rows
>> at all. Since the current date ("now") is either greater than the
>> maximum date in the table, or it is not, and you're not comparing to a
>> column in each row, only the aggregate, the resulting condition will
>> either be true for every row in the table, or false for every row.
>>
>>
>> Brad
>
>
> As Brad said, your query doesn't make much sense as is, but you can
> still do it like so --
>
> [10:15 PM] ~/Sites$sqlite3
> SQLite version 3.5.9
> Enter ".help" for instructions
> sqlite> create table foo (a);
> sqlite> insert into foo values ('2008-12-01');
> sqlite> insert into foo values ('2008-12-02');
> sqlite> insert into foo values ('2008-12-03');
> sqlite> select * from foo;
> 2008-12-01
> 2008-12-02
> 2008-12-03
> sqlite> select max(a) from foo;
> 2008-12-03
> sqlite> select min(a) from foo;
> 2008-12-01
> sqlite> select * from foo where date('now') > (select max(a) from foo);
> 2008-12-01
> 2008-12-02
> 2008-12-03
> sqlite> select * from foo where date('now') < (select max(a) from foo);
> sqlite>
>
>
> --
> Puneet Kishor http://www.punkish.org/
> Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
> Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
--
View this message in context:
http://www.nabble.com/Struggling-with-datetime%28%22now%22%29-%3E-MAX%28dtEndDate%29-query---Please-Help-tp20837020p20838039.html
Sent from the SQLite mailing list archive at Nabble.com.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
