Re: [sqlite] Multiple indexes in SQLite, and selecting which to use
On 17/08/2009 2:37 PM, Dan Kennedy wrote: On Aug 17, 2009, at 11:05 AM, John Machin wrote: On 17/08/2009 11:41 AM, Shane Harrelson wrote: INDEXED BY doesn't allow you to specify which index to use. It just causes the query to fail if SQLite thinks it should use an index different then the one specified by the INDEXED BY clause. Oh. The docs say If index-name does not exist or cannot be used for the query, then the preparation of the SQL statement fails. Please consider submitting a docs bug report (with evidence). The two statements are not incompatible. AFAICT, NONE of the scenarios you outline below fit Shane's statement It just causes the query to fail if SQLite thinks it should use an index different then the one specified by the INDEXED BY clause. For a scenario to match that statement, there must be at least TWO indexes on the table. Only one scenario has two indexes. In that case, whether SQLite thinks (looks at the analyze results) or not, you say that the result is that it goes with the index in the INDEXED BY clause ... so your description of that scenario doesn't match Shane's statement at all. On the other hand, all your scenarios match the above doc excerpt -- provided of course that the use is interpreted as use beneficially/advantageously. What happens in the code is that for a table with an INDEXED BY clause attached, SQLite does not consider any other indexes or a linear scan when planning a query. SQLite does not consider any other indexes is NOT compatible with Shane's SQLite thinks it should use an index different SQLite will not do a full scan of an index (unless this helps with an ORDER BY clause). If this means no valid plan is found, query compilation fails. So if you try this: CREATE TABLE t1(a, b); CREATE INDEX i1 ON t1(a); SELECT * FROM t1 INDEXED BY i1; Then the SELECT statement fails (to prepare) as SQLite cannot find a plan where it can use index i1 (advantageously). Yes, indeed, that select statement constitutes a user bug; it is a nonsense, quite irrespective of the presence/absence of i1 or any other index, creating/deleting ANALYZE results, or SQLite version changes. But if you do this: SELECT * FROM t1 INDEXED BY i1 ORDER BY a; Then this will prepare and run fine. The following will also work: CREATE INDEX i2 ON t1(b); SELECT * FROM t1 INDEXED BY t1 WHERE b=10 ORDER BY a; In this case, depending on the stats collected by any ANALYZE command, SQLite will normally use index i2 to optimize the b=10 constraint. But with the INDEXED BY, it uses index i1 to optimize the ORDER BY instead. Presumably the user knows something about the contents of table t1 that has allowed her to conclude that using index i1 will be more efficient in this case. The INDEXED BY feature was introduced to address concerns that SQLite might suddenly start using a different plan for a query in the field than it did in the office during testing. Either because somebody ran ANALYZE, or because the SQLite version was upgraded. In this situation, some users consider it better to throw an exception than to run the query with a different, possibly slower, plan. Confusion reigns supreme. Your second last paragraph says (about your last scenario) that it uses index i1 instead of the apparently better index i2 -- no exception throwing. Your last paragraph indicates that in this case an exception would be thrown. The docs are likewise confused -- after the early piece that I quoted (which supports using the INDEXED BY index unless it has vanished or would result in a full scan), we find this The intent of the INDEXED BY clause is to raise a run-time error if a schema change, such as dropping or creating an index, causes the query plan for a time-sensitive query to change. The INDEXED BY clause is designed to help detect undesirable query plan changes during regression testing. SUMMARY: Three worries with INDEXED BY i1: (1) i1 doesn't exist = no argument, raise an exception. (2) i1 cannot be used advantageously e.g. there is nothing in a WHERE clause or ORDER BY clause that fits i1 = no argument, raise an exception. (3) i1 exists and could be used (as in better than no index at all) but there is another index i2 that looks even better = option (a) use i1; (b) raise an exception IMHO whichever of (a) and (b) actually happens, some clarification in the docs might be useful. Cheers, John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple indexes in SQLite, and selecting which to use
The INDEXED BY feature was introduced to address concerns that SQLite might suddenly start using a different plan for a query in the field than it did in the office during testing. Either because somebody ran ANALYZE, or because the SQLite version was upgraded. In this situation, some users consider it better to throw an exception than to run the query with a different, possibly slower, plan. Confusion reigns supreme. Your second last paragraph says (about your last scenario) that it uses index i1 instead of the apparently better index i2 -- no exception throwing. Your last paragraph indicates that in this case an exception would be thrown. I guess I got that wrong then. Said users considered it better to throw an error if the index that the author of the SQL query expected it to use had been removed or radically altered. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [Duplicates] How to keep only one row?
Hello I have a table that has a lot of duplicates in the Name column. I'd like to only keep one row for each. The following lists the duplicates, but I don't know how to delete the duplicates and just keep one: SELECT name FROM members GROUP BY name HAVING COUNT(*) 1; Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] querying r-trees
Dear all, I would like to discuss a new feature in the SQLite R-tree which is not very difficult to implement but would improve query performance a lot for use cases where the MBR (minimum bounding rectangle) of the query object leads to a too large candidate set. First of all the data structure in the database can stay at it is, also the R*-tree splitting algorithm. The only thing I would like to see is that it is possible to register a function at the R-tree module which returns true or false and which gets as parameters the currently to be evaluated R*-tree rectangle and a void pointer representing the query object. Thus it would be possible to evaluate more complex query geometries during R-tree traversal. The disadvantage of the approach is that the intersection tests between more complex query objects and an r-tree rectangle is more time consuming (higher cpu cost for one test) but on the other hand the resulting candidate set would be much smaller (smaller i/o cost and less cpu-intersection tests). The current approach could also be modelled this way, if the testing simply compares two rectangles and returns true if they intersect. So the question is whether the intersection testing between query object and R-tree bounding rectangle cannot be passed to the r-tree module as user-defined function and then be applied by the r-tree module during tree traversal. Any thoughts on this? Best Martin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problem with sqlite3_column_origin_name and AS Clause
Hello to all SQLite people (that's my first post - please be patient:-)) I already searched the mailings from 2008/2009 but could not find a post that describes this problem. I have a select over a few tables, and since many tables use identical names for some columns (like Id), I rename the column using AS clause: SELECT Dokument.DokId AS Id, Patient.Name AS PatientName, Einsender.Name FROM Dokument, Patient, Einsender WHERE Dokument.AkteId = Patient.Id AND Dokument.SenderId = Einsender.Id; Unfortunately when I get the column names by function sqlite3_column_origin_name(), the result columns have the names DokId, Name and Name again - instead of Id, PatientName and Name!! For me this is severe because I pass back the whole result set of a query as one nice object and the column names have to be unique! (hope you understand what I'm saying) Is it possible to fix this bug or is there a workaround? (a VIEW does not help either) Thanks for any help! Dinu == DAISY Archivierungssysteme GmbH Preysingstr. 2 68526 Ladenburg www.adakta.de Tel: +49-6203-40400 Fax: +49-6203-404020 Mail: dinu.scheppelm...@adakta.de HRB: 432841 Amtsgericht Mannheim Geschäftsführer: Dr. Dinu Scheppelmann == ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Functions to convert dates with non-English month name?
Hello, Before I go ahead and write a script to loop through all the rows, I was wondering if SQLite supports functions to convert DD MM into the MySQL-friendly -MM-DD, and whether those functions are localized so that it understands month names in languages other than English? Here's an example: SELECT dateinscription, dateconnexion FROM membres LIMIT 1; 26 Mai 2007|17 Août 2009 - 09h20 I'd like to update the row into 2007-05-26 and 2009-08-17 09:20, respectively. Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [Duplicates] How to keep only one row?
Hi, If you are looking for a delete - command, then you have to decide which of the duplicate rows you want to keep. Are they all the same even in the other fields? Perhaps you want to do something like delete from members where exists (select rowid from members m2 where m2.name = members .name and m2.rowid members .rowid) This statement deletes all rows where there exists a row with the same name and a lowe rowid. Rowid is an internal field of sqlite. Martin Gilles Ganault wrote: Hello I have a table that has a lot of duplicates in the Name column. I'd like to only keep one row for each. The following lists the duplicates, but I don't know how to delete the duplicates and just keep one: SELECT name FROM members GROUP BY name HAVING COUNT(*) 1; Thank you. ___ 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] debugging sqlite with gdb
Mohammad Reaz Uddin wrote: I downloaded 'sqlite-amalgamation-3.6.16.tar.gz' and used makefile to compile it. sqlite is a shell script, wrapper to a compiled binary in .libs directory. -- Zaga You have worked and not worked. Not working is the hardest work of all. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Functions to convert dates with non-English month name?
Gilles Ganault wrote: Before I go ahead and write a script to loop through all the rows, I was wondering if SQLite supports functions to convert DD MM into the MySQL-friendly -MM-DD, and whether those functions are localized so that it understands month names in languages other than English? SQLite supports such functions in the sense that you can write a custom function that does anything you want, and use it in your statements. Here's an example: SELECT dateinscription, dateconnexion FROM membres LIMIT 1; 26 Mai 2007|17 Août 2009 - 09h20 I'd like to update the row into 2007-05-26 and 2009-08-17 09:20, respectively. If you need to do it once, you can do something like update membres set dateinscription= substr(dateinscription, -4) || '-' || (case substr(dateinscription, 4, length(dateinscription) - 8) when 'January' then '01' when 'February' then '02' ... when 'December' then '12' end) || '-' || substr(dateinscription, 1, 2); Substitute correct month names. Converting dateconnexion is left as an exercise for the reader. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with sqlite3_column_origin_name and AS Clause
Dinu Scheppelmann (DAISY) wrote: Unfortunately when I get the column names by function sqlite3_column_origin_name(), the result columns have the names DokId, Name and Name again - instead of Id, PatientName and Name!! You want sqlite3_column_name Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple indexes in SQLite, and selecting which to use
On Mon, Aug 17, 2009 at 3:15 AM, Dan Kennedy danielk1...@gmail.com wrote: The INDEXED BY feature was introduced to address concerns that SQLite might suddenly start using a different plan for a query in the field than it did in the office during testing. Either because somebody ran ANALYZE, or because the SQLite version was upgraded. In this situation, some users consider it better to throw an exception than to run the query with a different, possibly slower, plan. Confusion reigns supreme. Your second last paragraph says (about your last scenario) that it uses index i1 instead of the apparently better index i2 -- no exception throwing. Your last paragraph indicates that in this case an exception would be thrown. I guess I got that wrong then. Said users considered it better to throw an error if the index that the author of the SQL query expected it to use had been removed or radically altered. Dan. Sorry for muddying the waters. I was just trying to make clear that INDEXED BY isn't intended to be used as a tuning mechanism for index selection. My statement should have been clearer. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does PRAGMA synchronous=OFF ensure that no synching is done for the entire session?
Matt Sergeant wrote: On Fri, 14 Aug 2009 12:33:30 -0400, Angus March wrote: I want my INSERT done right away, I just don't want it to be flushed from the filesystem's write-behind cache until the kernel decides, not when SQLite decides. Did you mean you do want it to be flushed from the filesystem's write-behind cache when the kernel decides (rather than when SQLite decides)? That is one implication, yes. Because yes, that's what synchronous=OFF means. It stops SQLite from issuing fflush calls (effectively). Right, and this is implied by the documentation, but I was concerned that the documentation might be playing fast and loose, saying that fflush (or fsync, or fdatasync) won't be called, when it really means that it won't be called during any call to step() or finalize(), while it would be called when the session is closed. I wasn't sure, so I thought I'd ask, because it'll matter to my app. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does PRAGMA synchronous=OFF ensure that no synching is done for the entire session?
On Mon, 17 Aug 2009 10:47:23 -0400, Angus March wrote: Because yes, that's what synchronous=OFF means. It stops SQLite from issuing fflush calls (effectively). Right, and this is implied by the documentation, but I was concerned that the documentation might be playing fast and loose, saying that fflush (or fsync, or fdatasync) won't be called, when it really means that it won't be called during any call to step() or finalize(), while it would be called when the session is closed. I wasn't sure, so I thought I'd ask, because it'll matter to my app. Kernels will fflush when a file handle is closed, which will happen when you close the database handle. __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does PRAGMA synchronous=OFF ensure that no synching is done for the entire session?
Matt Sergeant wrote: On Mon, 17 Aug 2009 10:47:23 -0400, Angus March wrote: Because yes, that's what synchronous=OFF means. It stops SQLite from issuing fflush calls (effectively). Right, and this is implied by the documentation, but I was concerned that the documentation might be playing fast and loose, saying that fflush (or fsync, or fdatasync) won't be called, when it really means that it won't be called during any call to step() or finalize(), while it would be called when the session is closed. I wasn't sure, so I thought I'd ask, because it'll matter to my app. Kernels will fflush when a file handle is closed, which will happen when you close the database handle. Actually, looking at the man pages for fflush just the user-space buffers are flushed, and not the write-behind buffer in the kernel. If that's all SQLite does, that's ok. So again, if anyone knows that synchronous=OFF means that SQLite will not *deliberately* flush the kernel's write-behind cache *at any time* for the *rest of the session*, please let me know. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.6.17 test failure
--- On Sat, 8/15/09, Dan Kennedy danielk1...@gmail.com wrote: From: Dan Kennedy danielk1...@gmail.com Subject: Re: [sqlite] 3.6.17 test failure To: General Discussion of SQLite Database sqlite-users@sqlite.org Date: Saturday, August 15, 2009, 12:36 AM On Aug 15, 2009, at 2:14 AM, Ken wrote: I'm not sure if this an issue or not. make test failed with the following: 2 errors out of 40872 tests Failures on these tests: rollback-2.3 tkt3457-1.4 All memory allocations freed - no leaks Memory used: now 0 max 102680 max-size 2800336 Page-cache used: now 0 max 13 max-size 4096 Page-cache overflow: now 0 max 3071416 Scratch memory used: now 0 max 0 Scratch overflow: now 0 max 33296 max-size 33296 Maximum memory usage: 102680 bytes Current memory usage: 0 bytes Number of malloc() : -1 calls make: *** [test] Error 1 Both tests use Tcl to access a journal file in the file-system. What additional information is found in the body of the test log? (search for the strings rollback-2.3 and tkt3457-1.4). Dan. rollback-2.1... Ok rollback-2.2... Ok rollback-2.3... Expected: [0] Got: [1] rollback-2.4... Ok Memory used: now 16 max2775504 max-size 10 Page-cache used: now 0 max 13 max-size 4096 Page-cache overflow: now 0 max2643024 Scratch memory used: now 0 max 0 Scratch overflow: now 0 max 33296 max-size 33296 tkt3457-1.1... Ok tkt3457-1.2... Ok tkt3457-1.3... Ok tkt3457-1.4... Expected: [1 {unable to open database file}] Got: [0 {1 2 3 4 5 6}] tkt3457-1.5... Ok Memory used: now 16 max3874864 max-size102 Page-cache used: now 0 max 13 max-size 4096 Page-cache overflow: now 0 max3071416 Scratch memory used: now 0 max 0 Scratch overflow: now 0 max 33296 max-size 33296 Hope that helps ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] debugging sqlite with gdb
On 17 Aug 2009, at 12:46am, Miroslav Zagorac wrote: Mohammad Reaz Uddin wrote: I downloaded 'sqlite-amalgamation-3.6.16.tar.gz' and used makefile to compile it. sqlite is a shell script, wrapper to a compiled binary in .libs directory wrapped in a mystery, inside an enigma. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does PRAGMA synchronous=OFF ensure that no synching is done for the entire session?
On 17 Aug 2009, at 3:47pm, Angus March wrote: I was concerned that the documentation might be playing fast and loose, saying that fflush (or fsync, or fdatasync) won't be called, when it really means that it won't be called during any call to step() or finalize(), while it would be called when the session is closed. I wasn't sure, so I thought I'd ask, because it'll matter to my app. Although the SQLite code might not be syncing, it is still issuing the commands to write data to the database file (at least I assume it does). Various events can send a synchronisation event all the way up (or down) the chain of command. For instance, a hard disk which goes to sleep due to inactivity will trigger a sync before they sleep; a laptop which is about to sleep due to low battery will often flush queued writes; in some operating systems the OS will issue a sync for files handled by a process about to be swapped out of active memory. So no, you cannot depend on no writing to disk just because you've told SQLite not to synchronise every transaction. If you don't want your record written to disk, don't issue the INSERT command. Perhaps you could use a virtual table or something. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does PRAGMA synchronous=OFF ensure that no synching is done for the entire session?
On Mon, Aug 17, 2009 at 11:53 AM, D. Richard Hipp d...@hwaci.com wrote: On Aug 17, 2009, at 11:41 AM, Matt Sergeant wrote: Kernels will fflush when a file handle is closed Not according to Ted Ts'o (creator of the Ext2/3/4 filesystems). See, for example, the extensive discussions of this at http://thunk.org/tytso/blog/2009/03/12/delayed-allocation-and-the-zero-length-file-problem/ http://thunk.org/tytso/blog/2009/03/15/dont-fear-the-fsync/ Ted says that it is widely believed among programmers that close() will sync a file, but in fact nothing in POSIX requires this and in fact Linux does not do it. Some hacks were added to ext4 in the 2.6.30 kernel release to mitigate the damage following a power loss when programs fail to fsync() prior to close(). But everybody agrees those changes are an ugly hack. In POSIX, the bottom line is this: The *only* way to force data to oxide is to call sync() or fsync(). Some kernels and/or some filesystems might sync at other times, but it is not something that you can rely on. D. Richard Hipp d...@hwaci.com To the original question though, with PRAGMA synchronous=OFF, SQLite will NOT do explicit fsync()'s. A exception to this occurs with attached DB's and a transaction; when the transaction is committed and the master journal is deleted, SQLite fsyncs the directory that contained the journal to ensure the delete is flushed. The only way around this fsync() is to compile with SQLITE_DISABLE_DIRSYNC. This might be changed in the future. -Shane ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does PRAGMA synchronous=OFF ensure that no synching is done for the entire session?
Shane Harrelson wrote: To the original question though, with PRAGMA synchronous=OFF, SQLite will NOT do explicit fsync()'s. A exception to this occurs with attached DB's and a transaction; when the transaction is committed and the master journal is deleted, SQLite fsyncs the directory that contained the journal to ensure the delete is flushed. The only way around this fsync() is to compile with SQLITE_DISABLE_DIRSYNC. This might be changed in the future. Right. If the only time fsync would be called is... whatever that jargon is up there, then there should be a problem, since I don't think I'm using that. Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems with max(datestamp) in subquery
David Bicking-2 wrote: As written, you were selecting any record with the correct date regardless of Ensemble or Steuck. David The following seem to work: SELECT * FROM tblZO_Haupt AS hpt WHERE hpt.zo_tblEnsemble = AND hpt.zo_tblStueck = ... AND hpt.datum = ( SELECT MAX(hpt_sub.datum) FROM tblZO_Haupt AS hpt_sub WHERE hpt_sub.zo_tblSaenger = hpt.zo_tblSaenger AND hpt_sub.zo_tblEnsemble = hpt.zo_tblEnsemble AND hpt_sub.zo_tblStueck = hpt.zo_tblStueck AND hpt_sub.datum = '2009-08-03 00:00:00'); But I didn't really get the deeper meaning of the three conditions in the subquery: hpt_sub.xyz = hpt.xyz? Leo -- View this message in context: http://www.nabble.com/Problems-with-max%28datestamp%29-in-subquery-tp24905950p25015087.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] [Duplicates] How to keep only one row?
On Mon, 17 Aug 2009 13:05:53 +0200, Martin Engelschalk engelsch...@codeswift.com wrote: If you are looking for a delete - command, then you have to decide which of the duplicate rows you want to keep. Are they all the same even in the other fields? Perhaps you want to do something like delete from members where exists (select rowid from members m2 where m2.name = members .name and m2.rowid members .rowid) This statement deletes all rows where there exists a row with the same name and a lowe rowid. Rowid is an internal field of sqlite. Thanks for the code :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Functions to convert dates with non-English month name?
On Mon, 17 Aug 2009 07:44:15 -0400, Igor Tandetnik itandet...@mvps.org wrote: update membres set dateinscription= substr(dateinscription, -4) || '-' || (case substr(dateinscription, 4, length(dateinscription) - 8) when 'January' then '01' when 'February' then '02' ... when 'December' then '12' end) || '-' || substr(dateinscription, 1, 2); Substitute correct month names. Converting dateconnexion is left as an exercise for the reader. Thanks, I'll give it a shot. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] What does SQLITE_IOERR_TRUNCATE mean, exactly?
I have a broken database file, with a journal. It is opened read-only (via sqlite3_open_v2()). The statement PRAGMA user version is prepared successfully. When sqlite3_step() is called, the return code is SQLITE_IOERR. The extended code is SQLITE_IOERR_TRUNCATE. What exactly does this mean? JAB -- John A. Belli Software Engineer Refrigerated Transport Electronics, Inc. http://www.rtelectronics.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems with max(datestamp) in subquery
On Mon, 2009-08-17 at 15:05 -0700, Leo Freitag wrote: David Bicking-2 wrote: As written, you were selecting any record with the correct date regardless of Ensemble or Steuck. David The following seem to work: SELECT * FROM tblZO_Haupt AS hpt WHERE hpt.zo_tblEnsemble = AND hpt.zo_tblStueck = ... AND hpt.datum = ( SELECT MAX(hpt_sub.datum) FROM tblZO_Haupt AS hpt_sub WHERE hpt_sub.zo_tblSaenger = hpt.zo_tblSaenger AND hpt_sub.zo_tblEnsemble = hpt.zo_tblEnsemble AND hpt_sub.zo_tblStueck = hpt.zo_tblStueck AND hpt_sub.datum = '2009-08-03 00:00:00'); But I didn't really get the deeper meaning of the three conditions in the subquery: hpt_sub.xyz = hpt.xyz? Leo That will work because it forces the subquery and main query to look at the same values for tblSaenger, tblEnsemble and tblStueck. David ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] debugging sqlite with gdb
Thanks everybody. On Mon, Aug 17, 2009 at 5:22 AM, Simon Slavin slav...@hearsay.demon.co.ukwrote: On 17 Aug 2009, at 12:46am, Miroslav Zagorac wrote: Mohammad Reaz Uddin wrote: I downloaded 'sqlite-amalgamation-3.6.16.tar.gz' and used makefile to compile it. sqlite is a shell script, wrapper to a compiled binary in .libs directory wrapped in a mystery, inside an enigma. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Md. Reaz Uddin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users