Re: [sqlite] Re: How do I do this
On Jan 14, 2008 9:09 AM, Vishal Mailinglist <[EMAIL PROTECTED]> wrote: > > sno | id | amount > > > 1| 1 | 200 > > > 2| 1 | 300 > > > 3 | 2 | 100 > > > 4 | 2 | 100 > > > 5 | 1 | 500 > What if I do not have control over sno i.e it is random or unpredictable , > I want to subtract it in order of occurrence. Like doing subtracion of sno 2 > and 5 and so on may be next occurance for id 1 is at sno 20 , then what. The simple solution is to select both rows separately and then subtract it in your application. Really, if it were me I'd do that. Or you can use sum(), the only problem is how to make the the amount to be subtracted negative. Something like below, where the amount with the lower sno is turned negative. select sum(case when sno=(select min(sno) from tableName where (sno=? and id=?) or (sno=? and id=?)) then -amount else amount end) from tableName where (sno=? and id=?) or (sno=? and id=?) M. Manese - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] any way to SELECT the 100th-104th row of a table?
select * from table limit (n-1),(m-n) n-1 because it is 0-based http://www.sqlite.org/lang_select.html On 10/8/07, Adam Megacz <[EMAIL PROTECTED]> wrote: > > Hello. This is probably a stupid question, but... > > Is there any way to include some phrase in a SELECT clause that will > match only the Nth-Mth rows of a table, for some values of N and M? > > Note that ROWID isn't what I'm looking for -- if you delete rows from > a table the ROWID no longer matches the "row number". > > Thanks, > > - a > > -- > PGP/GPG: 5C9F F366 C9CF 2145 E770 B1B8 EFB1 462D A146 C380 > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: auto_increment - why does it do the opposite?
Igor has answered this before. Roughly: 1. all tables has an implicit integer column named "rowid" that is auto increment 2. creating an integer primary key effectively "renames" rowid to that column, so in your case below fields id and rowid are the same IIRC drh replied something else, and since I'm not familiar with the internals I'm not sure if that is how it is implemented or an equivalent black-box model of how sqlite works. But since Igor is like the next authoritative guru here after drh himself, that explanation should be correct. Cheers, M. Manese On 8/9/07, Paul Harris <[EMAIL PROTECTED]> wrote: > I dislike answering myself, but no sooner I had sent that email, I tried this: > > sqlite> create table ghi (id integer primary key autoincrement, value text); > sqlite> insert into ghi (value) values ('justvalue'); > sqlite> insert into ghi (id,value) values (null,'withid'); > sqlite> select * from ghi; > id|value > 1|justvalue > 2|withid > sqlite> > > so, why doesn't auto_increment do either of the following more sensible > things: > 1) throw an error > 2) do what "autoincrement" does > > ? > > thanks > Paul > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Saving an in-memory database to file
Or, attach then INSERT-SELECT On 7/25/07, Mohd Radzi Ibrahim <[EMAIL PROTECTED]> wrote: How about dumping and import into new db? - Original Message - From: "Colin Manning" <[EMAIL PROTECTED]> To: Sent: Wednesday, July 25, 2007 7:05 AM Subject: [sqlite] Saving an in-memory database to file > Hi > > If I create an in-memory database (by opening with ":memory:"), then add > tables etc to it, is it possible to then write it to a disk file so it can > subsequently be used as a file-based db? > > Thanks > > > > -- > No virus found in this outgoing message. > Checked by AVG. Version: 7.5.476 / Virus Database: 269.10.14/912 - Release > Date: 22/07/2007 19:02 > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Syntax help with UPDATE in SQLite Database Browser
Maybe you really have to loop "outside" sqlite to align the rows & values. From the result you got and the UPDATE documentation, I can guess that the subselect in the assignment is flattened to a scalar. Unfortunately sqlite does not have something like update tbl1 set col=tbl2.col from tbl2 where tbl2.id=tbl1.id which would have been what you needed. Cheers, M. Manese On 6/8/07, Ellis Robin (Bundaberg) <[EMAIL PROTECTED]> wrote: Thanks John, that's what I thought too... My positioning on a row is dependent on 2 values, and without any better knowledge of SQLite I've tried implementing this: Update Parameter set ParameterValue = (select NewParams.parametervalue from Scenario, Link, Catchment, FunctionalUnit, FunctionalUnitDefinition, StandardFU, Parameter, NewParams where Scenario.ScenarioID = 1004 and Scenario.NetworkID = Link.NetworkID and Link.LinkID = Catchment.LinkID and FunctionalUnit.CatchmentID = Catchment.CatchmentID and FunctionalUnit.FunctionalUnitDefinitionID = FunctionalUnitDefinition.FunctionalUnitDefinitionID and FunctionalUnit.FunctionalUnitID = StandardFU.StandardFUID and StandardFU.RainfallRunoffModelID = Parameter.ModelID and Catchment.Name = NewParams.Subcatchments and FunctionalUnitDefinition.Name = NewParams.FU_name and Parameter.Parameter = NewParams.parameter) Where Parameter.ModelID = (select Parameter.ModelID from Scenario, Link, Catchment, FunctionalUnit, FunctionalUnitDefinition, StandardFU, Parameter, NewParams where Scenario.ScenarioID = 1004 and Scenario.NetworkID = Link.NetworkID and Link.LinkID = Catchment.LinkID and FunctionalUnit.CatchmentID = Catchment.CatchmentID and FunctionalUnit.FunctionalUnitDefinitionID = FunctionalUnitDefinition.FunctionalUnitDefinitionID and FunctionalUnit.FunctionalUnitID = StandardFU.StandardFUID and StandardFU.RainfallRunoffModelID = Parameter.ModelID and Catchment.Name = NewParams.Subcatchments and FunctionalUnitDefinition.Name = NewParams.FU_name and Parameter.Parameter = NewParams.parameter) and Parameter.Parameter = (select Parameter.Parameter from Scenario, Link, Catchment, FunctionalUnit, FunctionalUnitDefinition, StandardFU, Parameter, NewParams where Scenario.ScenarioID = 1004 and Scenario.NetworkID = Link.NetworkID and Link.LinkID = Catchment.LinkID and FunctionalUnit.CatchmentID = Catchment.CatchmentID and FunctionalUnit.FunctionalUnitDefinitionID = FunctionalUnitDefinition.FunctionalUnitDefinitionID and FunctionalUnit.FunctionalUnitID = StandardFU.StandardFUID and StandardFU.RainfallRunoffModelID = Parameter.ModelID and Catchment.Name = NewParams.Subcatchments and FunctionalUnitDefinition.Name = NewParams.FU_name and Parameter.Parameter = NewParams.parameter) However my results are the same with this attempt at row positioning. Both of the select statements used in the WHERE expression do retturn valid records. Any more hints? Rob - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Longest "real" SQL statement
I am the author of the package SQLiteDF for R (a statistical package), some sort of sqlite backed "data set". It's "raison d'etre" is to deal with very large datasets, which could be tables with thousands of columns. I am not much on the infinite length sql statement, but I need lots of columns in the result. I plan to hack my way into extending the syntax to something like select col1 ... col100 from table which is shorthand for select col1,col2,col3,..., col100. So a result set with lots of columns would be very nice. Thanks, M. Manese On 5/10/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: I'm looking for an upper bound on how big legitimate SQL statements handed to SQLite get to be. I'm not interested in contrived examples. I want to see really big SQL statements that are actually used in real programs. "Big" can be defined in several ways: * Number of bytes of text in the SQL statement. * Number of tokens in the SQL statement * Number of result columns in a SELECT * Number of terms in an expression If you are using really big SQL statements, please tell me about them. I'd like to see the actual SQL text if possible. But if your use is proprietary, please at least tell me how big your query is in bytes or tokens or columns or expression terms. Thanks. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Newbee Question
You have to link against sqlite's shared lib, e.g. in linux $ gcc -L/path/to/sqlite/stuffs -I/path/to/sqlite/stuffs -lsqlite prog.c (the 2nd is a capital i, the 3rd a small L) Cheers, M. Manese On 4/4/07, nshaw <[EMAIL PROTECTED]> wrote: I upgraded from 3.3.1.3 to 3.3.1.4. Up till now, I've been experimenting with SQLite3 via the CLP. Now, I'm trying to access a DB via a C program but I'm getting errors. Here's a small code fragment: #include #include #include "sqlite3.h" int main (int argc, char **argv) { sqlite3 *db; /* from "the definitive guide to sqlite */ char *zErr; int rc; char *sql; rc = sqlite3_open ("Family.db", &db); return 0; } The error I get is (.text+0x16): undefined reference to 'sqlite3_open'. Any help will be greatly appreciated. Thanks, Nick. -- View this message in context: http://www.nabble.com/Newbee-Question-tf3521982.html#a9825045 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Using AVG() Correctly
On 2/22/07, Rich Shepard <[EMAIL PROTECTED]> wrote: However, I'm stuck on the proper SQL syntax. A nudge in the right direction -- including pointers to the appropriate documentation -- would be much appreciated. The "rule of thumb" is that anything that appears in the group-by clause can appear directly in the select, while other columns are aggregated in the select. Best example using your particular case is Igor's answer: select cat, pos, avg(col1), avg(col2), ... from voting group by cat, pos; M. Manese - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] about default file permission of SQLite database file
This is not actually about SQLite. man umask M. Manese On 2/22/07, Shan, Zhe (Jay) <[EMAIL PROTECTED]> wrote: Hi, If to use SQLite to create a database in Linux, the database file will be granted permission 644 as default. Is this value hardcoded in the current version? Is it possible to change this default vaule, say to 664 or something else? Thanks. Jay - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Restrictions on table names
There is almost no restrictions, just put it inside square brackets create table foobar ([my $0.02 column] int, ...) M. Manese On 2/20/07, Pablo Santacruz <[EMAIL PROTECTED]> wrote: I'd like to know if there is any restriction on table names. Thanks in advance -- Pablo - To unsubscribe, send email to [EMAIL PROTECTED] -