Re: [sqlite] Date problem
On Fri, 2 Jan 2004, KL Chin wrote: > Can anyone help on this problem, why with the query below > > DELETE FROM ABC WHERE datein < '12/29/03'; > > All my record date from '1/1/04' to '1/6/04' were delete. > Where datein was date type. SQLite is typeless, see: http://www.sqlite.org/datatypes.html, paying particular attention to section 3.0 on "comparison and sort order." As a string, "1/1/04" is less than "12/29/03". If you store your dates in -MM-DD format, they'll sort & compare properly as you would expect. Michael - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Select from Select
On Sun, 19 Oct 2003, Brian Pugh wrote: > How many records in a certain Postcode area (Zip code to our > US friends!); > How many of those have telephones (ie, the field is blank if > they don't have a telephone); > How many with telephones can be canvassed (I have a field called > Canvass that has either a 'Y' or 'N' in there) > Table is called NewsData; Postcode field is called Postcode; > Telephone field is called Tel; Canvass field is called Canvass Try something like: select postcode, count(*) as num_records, sum(case when tel is not null then 1 else 0 end) as num_phones, sum(case when tel is not null and canvas='Y' then 1 else 0 end) as num_canvasable from NewsData group by postcode; Michael - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
