Re: [sqlite] need help with a query using datetime

2011-06-17 Thread Igor Tandetnik
Black, Michael (IS) wrote: > sqlite> create table t(d date); > sqlite> insert into t values('2011-12-31 09:00'); > sqlite> insert into t values('2011-12-31 12:15'); > sqlite> select d,substr(datetime(d,'-12 hours'),1,16) from t; > 2011-12-31 09:00|2011-12-30 21:00 > 2011-12-31 12:15|2011-12-31 00:

Re: [sqlite] need help with a query using datetime

2011-06-17 Thread Ruth Ivimey-Cook
On 17/06/2011 12:10, looki wrote: > First column holds the givin datetime from my table and the second column > should show the datetime from first row but 12 hours before. for example: > > '2011-12-31 09:00' '2011-12-30 21:00' > '2011-12-31 12:15' '2011-12-30 00:15' > ... > > looks simple but date

Re: [sqlite] need help with a query using datetime

2011-06-17 Thread Black, Michael (IS)
sqlite> create table t(d date); sqlite> insert into t values('2011-12-31 09:00'); sqlite> insert into t values('2011-12-31 12:15'); sqlite> select d,substr(datetime(d,'-12 hours'),1,16) from t; 2011-12-31 09:00|2011-12-30 21:00 2011-12-31 12:15|2011-12-31 00:15 Michael D. Black Senior Scientist

Re: [sqlite] Need help with a query

2005-09-08 Thread Nemanja Corlija
> If this worked: > > select fname, chng from t1 > where fname like 'file%' > group by fname > having vers = max(vers) Yes, indeed it would :) > select fname, chng > from t1 > inner join (select fname, max(vers) as maxvers from t1 where fname > like 'file%' group by fname) maxvers on >

Re: [sqlite] Need help with a query

2005-09-08 Thread John LeSueur
Nemanja Corlija wrote: Hi all, I guess this isn't really all that complex, but I just can't think of a query that does what I need. Here's an example table: CREATE TABLE t1 (fname TEXT, vers TEXT, chng INT, UNIQUE(fname,vers)); INSERT INTO t1 VALUES('file1', '1', 0); INSERT INTO t1 VALUES('fil