Re: [sqlite] Calculation between rows?
On 7/17/05, Darren Duncan <[EMAIL PROTECTED]> wrote: > Assuming there actually is a meta-data function like ROW_NUMBER(), > which is the ordinal value of the row in the query result, you can > just do something like this: > > SELECT > FROM ( >SELECT ROW_NUMBER() AS myrownum, sq.* >FROM AS sq > ) AS a FULL OUTER JOIN ( >SELECT ROW_NUMBER + 1 AS myrownum, ... >FROM AS sq > ) AS b ON b.myrownum = a.myrownum > ... > I suppose I could copy the data table into a temporary table with an AUTOINCREMENT field. (The row-set used to create the temporary table's contents would be ordered appropriately.) The SQLite documenataion quarantees AUTOINCREMENT fields to be "monotonically increasing". I'll try that out using the ideas you've given me. Thanks for the insight, Darren. Cheers, Bill
Re: [sqlite] Calculation between rows?
At 4:27 PM -0400 7/17/05, William Trenker wrote: I've been searching the web on such topics like "sql calculation between rows" but I'm not having much success. The best hint I've found is to construct a join between the table and itself but I can't see how to do that in a way that offsets one side of the join relative to the other side. Assuming there actually is a meta-data function like ROW_NUMBER(), which is the ordinal value of the row in the query result, you can just do something like this: SELECT FROM ( SELECT ROW_NUMBER() AS myrownum, sq.* FROM AS sq ) AS a FULL OUTER JOIN ( SELECT ROW_NUMBER + 1 AS myrownum, ... FROM AS sq ) AS b ON b.myrownum = a.myrownum ... So you do the join between the subquery and itself, and the "+1" causes a single row offset in the join. Note you may have to UNION ALL a single row to the top or bottom of each subquery in order to prevent losing the first/last row. -- Darren Duncan
[sqlite] Calculation between rows?
I have a simple data acquisition application that reads a byte counter and records it in an sqlite3 table. The table is defined as: create table data (timestamp, bytecount); The 'timestamp' field is the output of the standard c-library seconds-since-the-epoch time() function and the 'bytecount' field is the value of the byte counter at that time. The data is populated by a small routine that samples the input on an irregular time interval and inserts the timestamp and bytecount into the table resulting in rows like this: 1121484797|11234 1121484923|14583 1121485008|19267 1121484801|12789 1121485051|23005 1121485832|28436 etc. (Notice that the timestamps are not ordered at the time of data collection.) I am attempting to work out an SQL query that will return a row-set which is the data rate calculated between time-ordered samples. I.e. (bytes(i+1)-bytes(i)) / (timestamp(i+1)-timestamp(i)) where i is the row number from a sub-query that orders the rows by timestamp. How do I use SQL to "walk" through a table like this? Is there a better table design for this type of application? I've been searching the web on such topics like "sql calculation between rows" but I'm not having much success. The best hint I've found is to construct a join between the table and itself but I can't see how to do that in a way that offsets one side of the join relative to the other side. Thanks very much, Bill
Re: [sqlite] Error: file is encrypted or is not a database
Hi Gerard, Am Sonntag, 17. Juli 2005 14:44 schrieb Dr Gerard Hammond: > I wrote one app for MacOS and Win32 clients called 'SyBrowser' > (It was originally written to query Sybase db's but now does others as > well) It is currently restricted to SQlite 2 db's > > http://www.macsos.com.au Did I say Linux? :-) Thanks Karl
Re: [sqlite] Error: file is encrypted or is not a database
The Philips MP3 players use SQLite version 2.x. You probably downloaded version 3.x. Version 3.x will not read or write version 2.x database files. Try using version 2.8.16 instead of whatever version it is you downloaded. On Sun, 2005-07-17 at 12:08 +0200, Karl Sinn wrote: > Hi, > > I am completely new with SQLite. My friend has a mp3-Player (I think it was a > Phillips iPod like device) and asked me if I can put him some music on. > Unfortunately I discovered, that you can do it only with the Windows-Program > belonging to the device, because they have a database-file in which they > write all the information about the musicfiles stored on it. > > Curious, I looked inside the the databasefile with an editor an I found that > line: > ** This file contains an SQLite 2.1 database ** > > So I downloaded the file on my Linux-computer. > > Today I downloaded SQLite and I tried to open the file with the > commandline-tool. > > When I do .tables or .databases I get this error: > Error: file is encrypted or is not a database > > I searched the mailing-list -archive, but did not find any hint. > What is the problem? > May it be that I copied the file from the device to my computer? > > Thanks for any hint > Karl Sinn >
