[sqlite] INVALID COMMAND NAME sqlite3_soft_heap_limit
Hello all, While i am trying to run the all.test file through the tcl shell I am getting the following error # tclsh all.test invalid command name sqlite3_soft_heap_limit while executing sqlite3_soft_heap_limit $soft_limit (file ./tester.tcl line 50) invoked from within source $testdir/tester.tcl (file all.test line 16) # how can i getrid of this problem Thanks in advance regards Chandru ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] About Sqlite cache
Hi all, I have some question about Sqlite cache: 1. In the Sqlite docs, It's said : If you are doing UPDATEshttp://www.sqlite.org/lang_update.htmlor DELETEs http://www.sqlite.org/lang_delete.html that change many rows of a database and you do not mind if SQLite uses more memory, you can increase the cache size for a possible speed improvement. Why only UPDATEs http://www.sqlite.org/lang_update.html or DELETEshttp://www.sqlite.org/lang_delete.html? Does INSERTs or SELECTs speed have no relationship with Sqlite cache? 2. Cache was malloced in a transaction, and was freed at the end of a transaction, is right? 3. Please intrduce the implement of cache mechanism in Sqlite. Thanks very much! Yoky ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INVALID COMMAND NAME sqlite3_soft_heap_limit
On Dec 22, 2008, at 3:55 PM, mkrajachan...@gmail.com wrote: Hello all, While i am trying to run the all.test file through the tcl shell I am getting the following error # tclsh all.test invalid command name sqlite3_soft_heap_limit while executing sqlite3_soft_heap_limit $soft_limit (file ./tester.tcl line 50) invoked from within source $testdir/tester.tcl (file all.test line 16) # how can i getrid of this problem Thanks in advance You need to build and use 'testfixture', not the regular sqlite3 tcl shell, to run the test suite. Are you doing so? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Fetch first non-sequential record
Good evening. Due to many deletions, my database is non sequential anymore. Is there any way to fetch the very first record in the database and find the id number? Or for that matter, a function to select the last record? I've tried the SELECT TOP 1 function butt that apparently isn't sqlite syntax. -- View this message in context: http://www.nabble.com/Fetch-first-non-sequential-record-tp21125757p21125757.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
Re: [sqlite] Fetch first non-sequential record
On Dec 22, 2008, at 5:43 PM, flakpit wrote: Good evening. Due to many deletions, my database is non sequential anymore. Is there any way to fetch the very first record in the database and find the id number? Or for that matter, a function to select the last record? I've tried the SELECT TOP 1 function butt that apparently isn't sqlite syntax. The 'first record' is a malleable concept. You can find the record with the lowest rowid value with: SELECT ... FROM table ORDER BY rowid LIMIT 1; -- View this message in context: http://www.nabble.com/Fetch-first-non-sequential-record-tp21125757p21125757.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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite under windows
Good Morning I have some problem with sqlite. I can't use it. I googled a lot after examples but i just found how to use it under linux with gcc. Where can I find static library to link in or how sould I use the dll? It's needed to make pointers(with GetProccAddress) to each function which i'd use? I use Dev-C++ under Win XP. Thanks Very Much! Dancsa ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Atomicity with triggers.
On Dec 22, 2008, at 6:08 PM, Srinivas Nayak wrote: Hi All, I am executing a SQL query DELETE using the function sqlite3_exec( ). I have a AFTER trigger that will be activated by my SQL query DELETE that is being executed. I have a doubt on the execution of sqlite3_exec( ). Will the function sqlite3_exec( ) return after the 'actions of DELETE query' and 'actions of all the activated triggers' are successfully completed? Yes. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fetch first non-sequential record
Dan Kennedy-4 wrote: Is there any way to fetch the very first record in the database and find the id number? The 'first record' is a malleable concept. You can find the record with the lowest rowid value with: SELECT ... FROM table ORDER BY rowid LIMIT 1; Thank you, that worked. Two questions spring to mind however.. 1. How many records are processed by SQLITE to get you the lowest rowid value this way, does it have any significant impact on memory? 2. The above statement gets the lowest rowid but there appears to be no implicit statement to the direction of the ORDER BY clause. Can the statement be reversed to get the highest rowid? Thanks for all your help. -- View this message in context: http://www.nabble.com/Fetch-first-non-sequential-record-tp21125757p21126226.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
Re: [sqlite] SQLite under windows
On Dec 22, 2008, at 6:10 PM, Dancsa wrote: Good Morning I have some problem with sqlite. I can't use it. I googled a lot after examples but i just found how to use it under linux with gcc. Where can I find static library to link in or how sould I use the dll? Easiest way to use sqlite is to download the amalgamation package: http://www.sqlite.org/sqlite-amalgamation-3_6_7.zip The zip file contains a source file called sqlite3.c that contains all of the sqlite code. Compile this file along with the other C files in your application. It's needed to make pointers(with GetProccAddress) to each function which i'd use? I use Dev-C++ under Win XP. Thanks Very Much! Dancsa ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Atomicity with triggers.
Hi All, I am executing a SQL query DELETE using the function sqlite3_exec( ). I have a AFTER trigger that will be activated by my SQL query DELETE that is being executed. I have a doubt on the execution of sqlite3_exec( ). Will the function sqlite3_exec( ) return after the 'actions of DELETE query' and 'actions of all the activated triggers' are successfully completed? Or it will return just after the 'actions of DELETE query' is successfully completed. Thanks and regards. Yours sincerely, Srinivas Nayak ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fetch first non-sequential record
On Dec 22, 2008, at 6:18 PM, flakpit wrote: Dan Kennedy-4 wrote: Is there any way to fetch the very first record in the database and find the id number? The 'first record' is a malleable concept. You can find the record with the lowest rowid value with: SELECT ... FROM table ORDER BY rowid LIMIT 1; Thank you, that worked. Two questions spring to mind however.. 1. How many records are processed by SQLITE to get you the lowest rowid value this way, does it have any significant impact on memory? Just 1. It's a very efficient query. 2. The above statement gets the lowest rowid but there appears to be no implicit statement to the direction of the ORDER BY clause. Can the statement be reversed to get the highest rowid? SELECT ... FROM table ORDER BY rowid DESC LIMIT 1; Details at: http://www.sqlite.org/lang_select.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fetch first non-sequential record
2. The above statement gets the lowest rowid but there appears to be no implicit statement to the direction of the ORDER BY clause. Can the statement be reversed to get the highest rowid? SELECT ... FROM table ORDER BY rowid DESC LIMIT 1; Okay, I feel very stupid now, hadn't twigged. I was typing in DESCENDING rather than DESC and didn't know why it was failing (LOL) I think I will go to bed now. -- View this message in context: http://www.nabble.com/Fetch-first-non-sequential-record-tp21125757p21126513.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
[sqlite] Id when using select statement (Noob)
Is there a way to get select and update statements show to rows ids in temporary sorted order For example: SELECT * FROM Persons ORDER BY LastName DESC P_Id LastName FirstName Address City 2 Svendson Tove Borgvn 23 Sandnes 3 Pettersen Kari Storgt 20 Stavanger 4 Nilsen Tom Vingvn 23 Stavanger 1 Hansen Ola Timoteivn 10 Sandnes Ideally it would work like: Tempid LastName FirstName Address City 1 Svendson Tove Borgvn 23 Sandnes 2 Pettersen Kari Storgt 20 Stavanger 3 Nilsen Tom Vingvn 23 Stavanger 4 Hansen Ola Timoteivn 10 Sandnes Is there a way of creating this somehow? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] rtree segfault on Linux X86_64
I get a segfault using a particular rtree query. Here's the simplest way I could find to reproduce it. $ uname -a Linux odysseus 2.6.18-6-xen-vserver-amd64 #1 SMP Fri Jun 6 07:07:31 UTC 2008 x86_64 GNU/Linux $ sqlite3 -version 3.6.7 $ cat bug.sql create table foo (id integer primary key); create virtual table bar using rtree (id, minX, maxX, minY, maxY); insert into foo values (null); insert into foo select null from foo; insert into foo select null from foo; insert into foo select null from foo; insert into foo select null from foo; insert into foo select null from foo; insert into foo select null from foo; delete from foo where id 40; -- change to 39,38,37,... and it won't segfault insert into bar select null,0,0,0,0 from foo; select count(*) from bar b1, bar b2, foo s1 where b1.minX = b2.maxX and s1.id = b1.id; $ sqlite3 bug.sql Segmentation fault I compiled sqlite with rtree from sqlite-amalgamation-3.6.7.tar.gz using gcc 4.1.2 20061115 (prerelease) (Debian 4.1.1-21). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Id when using select statement (Noob)
Tommy Benson tommyalph...@gmail.com wrote in message news:a1a7fb90812201757i4aa3339ma8489161aec89...@mail.gmail.com Is there a way to get select and update statements show to rows ids in temporary sorted order For example: SELECT * FROM Persons ORDER BY LastName DESC P_Id LastName FirstName Address City 2 Svendson Tove Borgvn 23 Sandnes 3 Pettersen Kari Storgt 20 Stavanger 4 Nilsen Tom Vingvn 23 Stavanger 1 Hansen Ola Timoteivn 10 Sandnes Ideally it would work like: Tempid LastName FirstName Address City 1 Svendson Tove Borgvn 23 Sandnes 2 Pettersen Kari Storgt 20 Stavanger 3 Nilsen Tom Vingvn 23 Stavanger 4 Hansen Ola Timoteivn 10 Sandnes Is there a way of creating this somehow? In your application, just have a counter and increment it every time you call sqlite3_step to get the next row. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE3_OPEN returns SQLITE_NOMEM
On Mon, Mar 24, 2008 at 3:13 PM, sqlite kartthikeya...@dexterity.in wrote: Dear All, I have just started using the SQLite Db in my applications. My application runs under MIPS processor. I have generated the Sqlite3.dll and Sqlite3.lib file for the MIPS processor and it is geting compiled. When i use Sqlit3_Open function to open a DB, it fails with the error message SQLITE_NOMEM. But the same code is running fine in the Windows mode. How should i rectify this? Can anyone help me in this regard. From another mail I received privately, it seems this is related to ticket #2479 [1] In a nutshell, some WinCE devices (either customized or old devices) don't know how to convert UTF-8 to UTF-16, so the utf8ToUnicode() and unicodeToUtf8() functions must be re-implemented (either using my untested patch or any other way). Also of interest is that Win95 and early Win98 machines also lack this conversion because it's an Internet Explorer upgrade that includes this capability to the system (but as every machine badly needs those IE upgrades, it should not be a problem). If you use only the UTF-16 API then it should work (if it's the same problem). Regards, ~Nuno Lucas [1] http://www.sqlite.org/cvstrac/tktview?tn=2479 Thanks Kartthi -- View this message in context: http://www.nabble.com/SQLITE3_OPEN-returns-SQLITE_NOMEM-tp16254109p16254109.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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INVALID COMMAND NAME sqlite3_soft_heap_limit
Hello Dan, Thank U for ur reply.. Could you please let me know more about testfixture. Also, please forward releated links Thanks Chandru On Mon, Dec 22, 2008 at 3:06 PM, Dan danielk1...@gmail.com wrote: On Dec 22, 2008, at 3:55 PM, mkrajachan...@gmail.com wrote: Hello all, While i am trying to run the all.test file through the tcl shell I am getting the following error # tclsh all.test invalid command name sqlite3_soft_heap_limit while executing sqlite3_soft_heap_limit $soft_limit (file ./tester.tcl line 50) invoked from within source $testdir/tester.tcl (file all.test line 16) # how can i getrid of this problem Thanks in advance You need to build and use 'testfixture', not the regular sqlite3 tcl shell, to run the test suite. Are you doing so? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] rtree segfault on Linux X86_64
On Dec 22, 2008, at 12:46 PM, Josh Purinton wrote: I get a segfault using a particular rtree query. Here's the simplest way I could find to reproduce it. Thanks for doing that. The patch linked from this page (click Patchset toward the top-left of the page) fixes things: http://www.sqlite.org/cvstrac/chngview?cn=6054 Or just grab a new copy of rtree.c from here: http://www.sqlite.org/cvstrac/getfile?f=sqlite/ext/rtree/rtree.cv=1.12 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Fwd: sqlite memory usage
Hello, My multi-threaded application has various sqlite db's open simultaneously, in memory using the :memory: keyword, disk based db's and at times, tmpfs (ram) db's. Is there a way to view each individual database's memory usage? I found the functions sqlite3_memory_used() and sqlite3_status(SQLITE_STATUS_MEMORY_USED, ...) but these look like they provide stats for the entire application, not per database. thanks, ed ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] UPDATE a ROW based on an UPDATE of a different ROW
Greetings! imagine this situation... CREATE TABLE foo (id, PID, bdate, edate, lang,job); INSERT INTO foo VALUES (1, 232,'2008-01-01','2008-01-10','es','trans'); INSERT INTO foo VALUES (2, 232,'2008-01-01','2008-01-10','fr','trans'); INSERT INTO foo VALUES (3, 232,'2008-01-01','2008-01-10','it','trans'); INSERT INTO foo VALUES (4, 232,'2008-01-01','2008-01-10','es','val'); INSERT INTO foo VALUES (5, 232,'2008-01-01','2008-01-10','fr','val'); INSERT INTO foo VALUES (6, 232,'2008-01-11','2008-01-11','it','val'); UPDATE foo SET bdate = (SELECT f2.edate FROM foo f1 JOIN foo f2 ON f1.PID = f2.PID AND f1.lang = f2.lang WHERE f1.job = 'val' AND f2.job = 'trans' AND f1.id = foo.id AND f1.bdate f2.edate) WHERE foo.job = 'val'; sqlite SELECT * FROM foo; 1|232|2008-01-01|2008-01-10|es|trans 2|232|2008-01-01|2008-01-10|fr|trans 3|232|2008-01-01|2008-01-10|it|trans 4|232|2008-01-10|2008-01-10|es|val 5|232|2008-01-10|2008-01-10|fr|val 6|232||2008-01-11|it|val sqlite As you can see, this UPDATE, UPDATE foo SET bdate = (SELECT f2.edate FROM foo f1 JOIN foo f2 ON f1.PID = f2.PID AND f1.lang = f2.lang WHERE f1.job = 'val' AND f2.job = 'trans' AND f1.id = foo.id AND f1.bdate f2.edate) WHERE foo.job = 'val'; clears the bdate of line 6, but 4 and 5 worked correctly. I did some searches on the internet to try to find out how to get it to work, but could not figure it out. I thought of a CASE, but couldn't figure it out. Newbie, of course. What I would like to do is to update the bdates of the 'val' jobs with the edate of the 'trans' job of the corresponding lang, only if the bdate of the 'val' job is the edate of the 'trans' job. I hope I am clear enough to get some help. thanks for any help you guys could provide, josé ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Kevin Smekens is out of the office.
I will be out of the office starting 2008/12/23 and will not return until 2009/01/05. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE a ROW based on an UPDATE of a different ROW
jose isaias cabrera cabr...@wrc.xerox.com wrote in message news:5c0942b2619c4403b47ddf72586c8...@stso.mc.xerox.com As you can see, this UPDATE, UPDATE foo SET bdate = (SELECT f2.edate FROM foo f1 JOIN foo f2 ON f1.PID = f2.PID AND f1.lang = f2.lang WHERE f1.job = 'val' AND f2.job = 'trans' AND f1.id = foo.id AND f1.bdate f2.edate) WHERE foo.job = 'val'; clears the bdate of line 6, but 4 and 5 worked correctly. I did some searches on the internet to try to find out how to get it to work, but could not figure it out. I thought of a CASE, but couldn't figure it out. Newbie, of course. What I would like to do is to update the bdates of the 'val' jobs with the edate of the 'trans' job of the corresponding lang, only if the bdate of the 'val' job is the edate of the 'trans' job. I hope I am clear enough to get some help. update foo set bdate = coalesce( (select f1.edate from foo f1 where f1.job='trans' and f1.PID=foo.PID and f1.lang=foo.lang and foo.bdate f2.edate), bdate) where job = 'val'; Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INVALID COMMAND NAME sqlite3_soft_heap_limit
If you use the supplied makefile, make fulltest should build the testfixture and run the all.test set. The testfixture is similar to the sqlite3 CLI except that it includes a TCL interpreter as well as an extensive set of test harnesses for virtually every part of the SQLite core. On Mon, Dec 22, 2008 at 9:05 AM, mkrajachan...@gmail.com wrote: Hello Dan, Thank U for ur reply.. Could you please let me know more about testfixture. Also, please forward releated links Thanks Chandru On Mon, Dec 22, 2008 at 3:06 PM, Dan danielk1...@gmail.com wrote: On Dec 22, 2008, at 3:55 PM, mkrajachan...@gmail.com wrote: Hello all, While i am trying to run the all.test file through the tcl shell I am getting the following error # tclsh all.test invalid command name sqlite3_soft_heap_limit while executing sqlite3_soft_heap_limit $soft_limit (file ./tester.tcl line 50) invoked from within source $testdir/tester.tcl (file all.test line 16) # how can i getrid of this problem Thanks in advance You need to build and use 'testfixture', not the regular sqlite3 tcl shell, to run the test suite. Are you doing so? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE a ROW based on an UPDATE of a different ROW
Igor Tandetnik wrote... As you can see, this UPDATE, UPDATE foo SET bdate = (SELECT f2.edate FROM foo f1 JOIN foo f2 ON f1.PID = f2.PID AND f1.lang = f2.lang WHERE f1.job = 'val' AND f2.job = 'trans' AND f1.id = foo.id AND f1.bdate f2.edate) WHERE foo.job = 'val'; clears the bdate of line 6, but 4 and 5 worked correctly. I did some searches on the internet to try to find out how to get it to work, but could not figure it out. I thought of a CASE, but couldn't figure it out. Newbie, of course. What I would like to do is to update the bdates of the 'val' jobs with the edate of the 'trans' job of the corresponding lang, only if the bdate of the 'val' job is the edate of the 'trans' job. I hope I am clear enough to get some help. update foo set bdate = coalesce( (select f1.edate from foo f1 where f1.job='trans' and f1.PID=foo.PID and f1.lang=foo.lang and foo.bdate f2.edate), bdate) where job = 'val'; thanks, Igor. josé ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] confusing with how to to this in sqlite
= Without the group by clause the result set is: sqlite select awal1.Code, awal1.Level, awal1.Category, awal1.Product, awal1.Location, awal1.Begin,akhir1.End, strftime(%s,akh ir1.End)-strftime(%s,awal1.Begin) as Difference from awal1, akhir1 where awal1.Code = akhir1.Code and akhir1.Category like awa l1.Category || '%' and awal1.Product = akhir1.Product and awal1.Location = akhir1.Location and akhir1.End = awal1.Begin; A1220SMALLFOODMARGARINEHOMS 12007-05-06 11:42:46 2007-05-06 11:42:460 A1221SMALLFOODCAKEHOMS 22007-05-06 11:31:57 2007-05-06 11:31:570 A1221SMALLFOODCAKEHOMS 12007-05-06 11:31:57 2007-05-06 11:31:570 A1221SMALLFOODCAKEHOMS 12007-05-06 11:31:57 2007-05-06 11:42:46649 A1221SMALLFOODCAKEHOMS 12007-05-06 11:42:46 2007-05-06 11:42:460 A1222SMALLFOODWAFERHOMS 22007-05-06 11:20:34 2007-05-06 11:31:57683 A1222SMALLFOODWAFERHOMS 12007-05-06 11:20:34 2007-05-06 11:31:57683 A1222SMALLFOODWAFERHOMS 12007-05-06 11:20:34 2007-05-06 11:42:461332 A1222SMALLFOODWAFERHOMS 12007-05-06 11:42:46 2007-05-06 11:42:460 A1236MEDIUMFOODSNACKHOMS 22007-05-06 10:48:57 2007-05-06 11:19:211824 A1236MEDIUMFOODSNACKHOMS 12007-05-06 10:48:57 2007-05-06 11:19:251828 A1269SMALLCLOTHESBELTHOMS 32007-05-07 17:28:25 2007-05-07 17:28:272 The group by clause combines rows A1221SMALLFOODCAKEHOMS 12007-05-06 11:31:57 2007-05-06 11:31:570 A1221SMALLFOODCAKEHOMS 12007-05-06 11:31:57 2007-05-06 11:42:46649 into 1 row. The values in the columns not included in the group by clause (Begin, End and Difference) could be from any of the combined rows (which rows is not, I believe, specified in any standard). MySql and Sqlite seem to result in different selections. If you want specific rows, then you need to modify the query to control the data selection. In this case it appears that: selectawal1.Code, awal1.Level, awal1.Category, awal1.Product, awal1.Location, awal1.Begin, min( akhir1.End ), min( strftime(%s,akhir1.End)-strftime(%s,awal1.Begin) ) as Difference fromawal1, akhir1 whereawal1.Code = akhir1.Code and akhir1.Category like awal1.Category || '%' and awal1.Product = akhir1.Product and awal1.Location = akhir1.Location and akhir1.End = awal1.Begin group byawal1.Begin, awal1.Code, awal1.Category, awal1.Product, awal1.Location; gives the result you want. Rgds, Simon = thanks to Simon. it works. but i have some question. i hope u can help me to explain these : 1. is it necessary or not to specify min(awal1.begin). 2. is min(strftime(%s,akhir1.End)-strftime(%s,awal1.Begin)) as Difference is more time consuming to compute than strftime(%s,min(akhir1.End))-strftime(%s,awal1.Begin) as Difference. i think these give the same result. 3. akhir1.Category like awal1.Category || '%'. what does it mean? why is use || '%'???. why not to write awal1.category = akhir1.category. it also give the same result. once again, i would say thank you for ur solution. sory for my bad english. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
