[sqlite] Subtotal SQL
SQL/sqlite challenge for all: I have a table with many columns describing objects on a disk. filename, date, owner, size A12MO100 B13 JAK90 C10 MO80 A13 LU 70 I have many different ways of ordering these objects. Usually using several sort parameters. I need a running sum of size that works regardless of what order the objects are in. Sort by date,owner Name SizeSum C80 80 A 100 180 B 90 270 A 70 340 Sort by filename, owner Name Size Sum A 7070 A 100 170 B 90 260 C 80340 User Function/Aggregates welcome! thanks Eliedaat Adler This message is confidential and intended only for the addressee. If you have received this message in error, please immediately notify the postmas...@nds.com and delete it from your system as well as any copies. The content of e-mails as well as traffic data may be monitored by NDS for employment and security purposes. To protect the environment please do not print this e-mail unless necessary. An NDS Group Limited company. www.nds.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sequential row numbers from query
Possible limitations of temporary tables vs views: 1) Temporary tables are static - views are dynamic - i.e. anytime a change is made to the database the temporary tables might need to be recreated. 2) Memory: Temporary tables will be retained in memory until dropped. Views will only use memory when used. Eli Adler -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Robert Citek Sent: Saturday, March 28, 2009 12:03 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] sequential row numbers from query That would work. In fact, my current solution, which actually pipes to perl, works pretty well. It's just that I have to then import the data back into the database. So, I'd prefer to do the process entirely in SQL. I was thinking maybe a view, but that didn't work. Apparently, there is no rowid with views (or is there something equivalent that I'm overlooking?). $ sqlite3 db 'create view bar as select * from foo order by field desc; select rowid, * from bar ; ' |c |b |a Substituting a temporary table for the view works: $ sqlite3 db 'create temporary table bat as select * from foo order by field desc; select rowid, * from bat ; ' 1|c 2|b 3|a Any limitations to consider when using a temporary table? Any other ideas? Regards, - Robert On Fri, Mar 27, 2009 at 3:37 PM, Thomas Briggs wrote: > Holy cow that feels inefficient. > > It's a bit clunky, but why not insert into a temporary table, > ordered as desired, and then use the rowid from the temp table? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This e-mail is confidential, the property of NDS Ltd and intended for the addressee only. Any dissemination, copying or distribution of this message or any attachments by anyone other than the intended recipient is strictly prohibited. If you have received this message in error, please immediately notify the postmas...@nds.com and destroy the original message. Messages sent to and from NDS may be monitored. NDS cannot guarantee any message delivery method is secure or error-free. Information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. We do not accept responsibility for any errors or omissions in this message and/or attachment that arise as a result of transmission. You should carry out your own virus checks before opening any attachment. Any views or opinions presented are solely those of the author and do not necessarily represent those of NDS. To protect the environment please do not print this e-mail unless necessary. NDS Limited Registered Office: One London Road, Staines,Middlesex TW18 4EX, United Kingdom. A company registered in England and Wales Registered no. 3080780 VAT no. GB 603 8808 40-00 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select statement - Need help
By side-effect the following statement should give those values: Select remoteId, hostName, max(lastUpdateTime) from (select * from table order by hostName, lastUpdateTime) Group by hostName having count(*) > 1 ; The outer select will return the last row processed by aggregate function max(lastUpdateTime) - i.e. the last row for each group. The internal select order guarantees that row will have max(lastUpdateTime) The more correct SQL would be something like: Select remoteId, t.hostname, lastUpdateTime from table, ( Select hostname, Max(lastUpdateTime) max_utime, count(*) cnt from tablegroup by hostName) host_max where table.hostname = host_max.hostname and table.lastUpdateTime = max_utime and cnt > 1 ; Eli -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joanne Pham Sent: Sunday, March 08, 2009 9:45 PM To: General Discussion of SQLite Database Subject: [sqlite] select statement - Need help Hi All, I have the folowing table which has the following data for example: remoteId hostNamelastUpdateTime 1host119 2 host111 3host222 4host333 5host449 6host444 So if I ran this statement below: select * from table group by hostName having count(*) > 1; I got the following rows: 2 host1 11 6 host4 44 But I want the rows which have bigger lastUpdateTime if hostName has duplicate row. So I want to return: 1 host1 19 2 host4 49 Would like to have sql statement to return the rows above. Thanks, JP ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This e-mail is confidential, the property of NDS Ltd and intended for the addressee only. Any dissemination, copying or distribution of this message or any attachments by anyone other than the intended recipient is strictly prohibited. If you have received this message in error, please immediately notify the postmas...@nds.com and destroy the original message. Messages sent to and from NDS may be monitored. NDS cannot guarantee any message delivery method is secure or error-free. Information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. We do not accept responsibility for any errors or omissions in this message and/or attachment that arise as a result of transmission. You should carry out your own virus checks before opening any attachment. Any views or opinions presented are solely those of the author and do not necessarily represent those of NDS. To protect the environment please do not print this e-mail unless necessary. NDS Limited Registered Office: One London Road, Staines,Middlesex TW18 4EX, United Kingdom. A company registered in England and Wales Registered no. 3080780 VAT no. GB 603 8808 40-00 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Better Update SQL
Hi, Exercise in SQL for all Is there a better way to phrase this INSERT trigger text: UPDATE ITEM SET ITEM_SGT= ifnull(ITEM_SGT,(SELECT SGT FROM SETUP)) , ITEM_EGT = ifnull(ITEM_EGT,(SELECT EGT FROM SETUP)), ITEM_SGT_MODE = ifnull(ITEM__AUTO_SGT_MODE,(SELECT SGT_MODE FROM SETUP)), ITEM_EGT_MODE = ifnull(ITEM_AUTO_EGT_MODE,(SELECT EGT_MODE FROM SETUP)) WHERE ITEM__ID = NEW.ITEM__ID; explanation: SETUP is a configurable table of defaults with a single row of values - when a row in inserted to ITEM - we want the trigger to populate the default values if they have not been set by INSERT. Eliedaat * This e-mail is confidential, the property of NDS Ltd and intended for the addressee only. Any dissemination, copying or distribution of this message or any attachments by anyone other than the intended recipient is strictly prohibited. If you have received this message in error, please immediately notify the [EMAIL PROTECTED] and destroy the original message. Messages sent to and from NDS may be monitored. NDS cannot guarantee any message delivery method is secure or error-free. Information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. We do not accept responsibility for any errors or omissions in this message and/or attachment that arise as a result of transmission. You should carry out your own virus checks before opening any attachment. Any views or opinions presented are solely those of the author and do not necessarily represent those of NDS. NDS Limited Registered office: One Heathrow Boulevard, 286 Bath Road, West Drayton, Middlesex, UB7 0DQ, United Kingdom. A company registered in England and Wales Registered no. 3080780 VAT no. GB 603 8808 40-00 To protect the environment please do not print this e-mail unless necessary. ** ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bulk insert
Executing BEGIN TRANSACTION before and END TRANSACTION after the full
insert will greatly improve
Your performance.
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Vincent Vega
Sent: Sunday, March 09, 2008 12:44 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Bulk insert
Hi all ,
I need to insert 500 records (each record has 12 bytes) to a table and
it takes me approximately 2 seconds.
Is there a way to improve my code so it can do it faster?
Thanks in advance,
Marco.
Here is my code:
query = sqlite3_mprintf("Insert into Inventory (Tag) values (?)");
rc=sqlite3_prepare_v2(DB,query ,-1,&Statement,NULL);
if (rc!=SQLITE_OK)
error_print();
for (i=1;500;i ++)
{
rc=sqlite3_bind_text(Statement,1,list[i],-1,NULL);
if (rc!=SQLITE_OK)
error_print();
rc=sqlite3_step(Statement);
if (rc!=SQLITE_DONE)
error_print();
sqlite3_reset(&Statement) ;
}
-
Never miss a thing. Make Yahoo your homepage.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
*
This e-mail is confidential, the property of NDS Ltd and intended for the
addressee only. Any dissemination, copying or distribution of this message or
any attachments by anyone other than the intended recipient is strictly
prohibited. If you have received this message in error, please immediately
notify the [EMAIL PROTECTED] and destroy the original message. Messages sent
to and from NDS may be monitored. NDS cannot guarantee any message delivery
method is secure or error-free. Information could be intercepted, corrupted,
lost, destroyed, arrive late or incomplete, or contain viruses. We do not
accept responsibility for any errors or omissions in this message and/or
attachment that arise as a result of transmission. You should carry out your
own virus checks before opening any attachment. Any views or opinions
presented are solely those of the author and do not necessarily represent those
of NDS.
NDS Limited Registered office: One Heathrow Boulevard, 286 Bath Road, West
Drayton, Middlesex, UB7 0DQ, United Kingdom. A company registered in England
and Wales Registered no. 3080780 VAT no. GB 603 8808 40-00
To protect the environment please do not print this e-mail unless necessary.
**
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] index with NULL values
Hi, I want to add an index on a sparsely populated column - i.e. for most rows the column is null. 1. Would this index be very compact? i.e. only include "set" rows. 2. Could this index also help select NULL entries? Thanks, Eliedaat Adler * This e-mail is confidential, the property of NDS Ltd and intended for the addressee only. Any dissemination, copying or distribution of this message or any attachments by anyone other than the intended recipient is strictly prohibited. If you have received this message in error, please immediately notify the [EMAIL PROTECTED] and destroy the original message. Messages sent to and from NDS may be monitored. NDS cannot guarantee any message delivery method is secure or error-free. Information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. We do not accept responsibility for any errors or omissions in this message and/or attachment that arise as a result of transmission. You should carry out your own virus checks before opening any attachment. Any views or opinions presented are solely those of the author and do not necessarily represent those of NDS. To protect the environment please do not print this e-mail unless necessary. **
RE: [sqlite] Help wiith SQL - first row of each group
Tricky it is - and original.
Thanks for all the suggestions - I'll try Ed's "trick" and Tomash's
function -
Another option - similar to Tomash's solution - is processing the rows
ordered by group and priority through a "collapsing" callback
That only "accepts" the first row per group.
Thanks again,
Eliedaat
-Original Message-
From: Ed Pasma [mailto:[EMAIL PROTECTED]
Sent: Monday, May 07, 2007 10:28 PM
To: Adler, Eliedaat
Cc: sqlite-users@sqlite.org
Subject: RE: [sqlite] Help wiith SQL - first row of each group
This solution may is tricky but has occasoinaly helped me. It is written
here dedicated for the example data. For real data the leftpadding
should likely be increased to the content of the sorting key. Also the
result may need to be converted to the expected data type, it has now
become text.
SELECT
g,
SUBSTR (MAX (SUBSTR (' ' || p, -2, 2) || v), 3, 1) v FROM
t
GROUP BY
g
;
***
This email message and any attachments thereto are intended only for use by the
addressee(s) named above, and may contain legally privileged and/or
confidential information. If the reader of this message is not the intended
recipient, or the employee or agent responsible to deliver it to the intended
recipient, you are hereby notified that any dissemination, distribution or
copying of this communication is strictly prohibited. If you have received this
communication in error, please immediately notify the [EMAIL PROTECTED] and
destroy the original message.
***
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Help wiith SQL - first row of each group
Thanks, I've tried that - and it works great -
except that "TEST" is the result of a very complex subquery on
100,000X1000 rows
"JOIN"ing TEST to TEST runs the entire subquery twice - effectively
doubling
the execution time (I have very limited cache space).
Any suggestions that "scans" TEST only once?
-Original Message-
From: Maulkye [mailto:[EMAIL PROTECTED]
Sent: Monday, May 07, 2007 4:35 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Help wiith SQL - first row of each group
How about something like this:
create table test
(
Grp text,
Val text,
Pri integer
)
insert into test values ('A', 'X', 1)
insert into test values ('A', 'X', 2)
insert into test values ('B', 'Y', 4)
insert into test values ('B', 'Z', 2)
insert into test values ('B', 'X', 8)
insert into test values ('C', 'Y', 6)
insert into test values ('C', 'Z', 8)
insert into test values ('C', 'X', 9)
insert into test values ('C', 'Y', 11)
select distinct pri.* from
(
select Grp, min(Pri) as 'Pri'
from test
group by [Grp]
) grp
join
(
select Grp, Val, Pri
from test
) pri
on grp.Grp=pri.Grp and grp.Pri=pri.Pri
Grp Val Pri
---
AX1
BZ2
CY6
- Original Message
From: "Adler, Eliedaat" <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, May 7, 2007 8:38:22 AM
Subject: [sqlite] Help wiith SQL - first row of each group
Hi guys,
I have a complex query result set RESULT_TABLE that returns:
GVP
___
AX1
AX2
BY4
BZ2
BX8
CY6
CZ8
CX9
CY11
G - defines groups
V - some value for that specific row
C - defined a display priority - i.e.
I need to define a query that returns only the "first' row in each group
- i.e. the row with the lowest display priority:
GVP
___
A X1
B Z 2
CY 6
- most preferably a query that doesn't require selecting RESULT_TABLE
more than once.
Thanks,
Eli
***
This email message and any attachments thereto are intended only for use
by the addressee(s) named above, and may contain legally privileged
and/or confidential information. If the reader of this message is not
the intended recipient, or the employee or agent responsible to deliver
it to the intended recipient, you are hereby notified that any
dissemination, distribution or copying of this communication is strictly
prohibited. If you have received this communication in error, please
immediately notify the [EMAIL PROTECTED] and destroy the original
message.
***
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
***
This email message and any attachments thereto are intended only for use by the
addressee(s) named above, and may contain legally privileged and/or
confidential information. If the reader of this message is not the intended
recipient, or the employee or agent responsible to deliver it to the intended
recipient, you are hereby notified that any dissemination, distribution or
copying of this communication is strictly prohibited. If you have received this
communication in error, please immediately notify the [EMAIL PROTECTED] and
destroy the original message.
***
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Help wiith SQL - first row of each group
Hi guys, I have a complex query result set RESULT_TABLE that returns: GVP ___ AX1 AX2 BY4 BZ2 BX8 CY6 CZ8 CX9 CY11 G - defines groups V - some value for that specific row C - defined a display priority - i.e. I need to define a query that returns only the "first' row in each group - i.e. the row with the lowest display priority: GVP ___ A X1 B Z 2 CY 6 - most preferably a query that doesn't require selecting RESULT_TABLE more than once. Thanks, Eli *** This email message and any attachments thereto are intended only for use by the addressee(s) named above, and may contain legally privileged and/or confidential information. If the reader of this message is not the intended recipient, or the employee or agent responsible to deliver it to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the [EMAIL PROTECTED] and destroy the original message. ***
RE: [sqlite] Finding relative position in VIEW
Nope - select rowid returns NULL for each row. -Original Message- From: Jay Sprenkle [mailto:[EMAIL PROTECTED] Sent: Monday, January 09, 2006 4:48 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Finding relative position in VIEW > > At present I have a "counting" callback function on "select myid from > myview" that returns the number of rows encountered before > myid=this_myid - severely inefficient. > > Any other ideas? Count the rows with a counter as you fetch them? I'm not sure if ROWID works on a view or not... does this work? select rowid, myview.* from myview *** This email message and any attachments thereto are intended only for use by the addressee(s) named above, and may contain legally privileged and/or confidential information. If the reader of this message is not the intended recipient, or the employee or agent responsible to deliver it to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the [EMAIL PROTECTED] and destroy the original message. ***
[sqlite] Finding relative position in VIEW
I have a view defined on a table: "create view myview as select myid from mytable where order by " This works beautifully - "select * from myview" always retrieves the rows in the sort order defined. Given a relative position "i" - I can easily retrieve "myid" for that position "select myid from myview limit 1 offset i" The challenge is to find the opposite: I.e. Given "this_myid" - somehow retrieve the relative position in the view. At present I have a "counting" callback function on "select myid from myview" that returns the number of rows encountered before myid=this_myid - severely inefficient. Any other ideas? Thanks, Eli *** This email message and any attachments thereto are intended only for use by the addressee(s) named above, and may contain legally privileged and/or confidential information. If the reader of this message is not the intended recipient, or the employee or agent responsible to deliver it to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the [EMAIL PROTECTED] and destroy the original message. ***
RE: [sqlite] limiting table size?
>Sean Wrote === >I'm considering using SQLLite for an embedded project. Some of the data I'd like to store is timestamped sensor readings. I'd like to know if there is a way to >configure a table so that it acts like a fixed length FIFO queue, e.g. stores 10,000 records then once full drops off the oldest record each time a new one is inserted. .>Can anyone tell me if we can do that with SQLLite? = You could use a simple trigger (example for queue of size 1 below) and a self-incrementing key column: create table myqueue (limit_id integer not null primary key, myvalue integer) ; create trigger queue_limit after insert on myqueue begin update myqueue set limit_id = limit_id-1 where (select max(limit_id) from myqueue ) > 1; delete from myqueue where limit_id <=0 ; end ; INSERT TO MYQUEUE: insert into myqueue (myvalue) values (1) ; insert into myqueue (myvalue) values(2) ; .. insert into myqueue (myvalue) values (10050) ; RESULTS: limit_idmyvalue 151 252 . 1 10050 Note: Its not very efficient. Regards, Eli *** This email message and any attachments thereto are intended only for use by the addressee(s) named above, and may contain legally privileged and/or confidential information. If the reader of this message is not the intended recipient, or the employee or agent responsible to deliver it to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the [EMAIL PROTECTED] and destroy the original message. ***
