Re: [sqlite] Order of UNION query results
On 22-01-11 00:53, Josh Gibbs wrote: Could someone please clarify for me if the the resulting order of a UNION query will come back with the left data first, then the right data in the case that no ordering has been defined for the query. My need is to have a parameter stored in a database, with an optional overriding parameter which should take precedence, such as: select value from param_overrides where key='setting' UNION select value from params where key='setting' I'd like the resulting recordset to always contain the override parameter first if it exists so I can simply use that value. Thanks, Josh ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users select value, 1 as myorder from param_overrides where key='setting' UNION select value, 2 as myorder from params where key='setting' order by 2; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] calculate difference of two times and show the result as double value
On 10-01-11 08:42, Sven wrote:
Dear SQLite users,
I've created a Microsoft Access 2003 database with three complex queries
written in pure SQL language. The function of the database is to
document and calculate the daily work-time.
Today I want to convert this database to SQLite but I have problems with
my written queries: How can I calculate the difference of two times and
show the result as double value?
For more information I'll include one example: One table hold the two
times (start_time, end_time) and the calculated result (shown in the
table as 'difference'):
[code]
start_time | end_time | difference
08:00:00 | 16:30:00 | 8.5
[/code]
The result of the difference between the two times (start_time,
end_time) shown in the table above as 'difference' will be calculated
with the following SQL-query code within Microsoft Access 2003:
[code]
( [end_time] - [start_time] ) * 24
[/code]
and returns the following data '8.5'. While using the following query
code within SQLite:
[code]
select time(end_time) - time(start_time)
[/code]
I only get the following data returned: '8' (but not '8.5' as calculated
with SQL).
So currently I don't know what to do next to solve my problem and
perhaps all the other users of this SQLite-users mailing list could help
me out.
best regards
select (strftime('%s', time('now', '4 minutes')) - strftime('%s',
time('now')));
returns: 240(4*60)
This was found via Google at:
http://www.mail-archive.com/sqlite-users@sqlite.org/msg56060.html
More help on the strftime function:
http://sqlite.org/lang_datefunc.html
http://pubs.opengroup.org/onlinepubs/007908799/xsh/strftime.html
--
Luuk
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] max length of integer?
On 10-01-11 13:09, Simon Slavin wrote: On 10 Jan 2011, at 10:05am, KimTaein wrote: What is the largest number that I can store in integer data type in sqlite? I was not able to find it on sqlite website. You're right. It's not in the obvious place. You can, however, find it here: http://www.sqlite.org/fileformat.html#varint_format If I understand it correctly, integers can take up 64 bits, so the largest number would be about 1.84467441e19 sqlite select typeof((9223372036854775807)) ; integer sqlite select typeof((9223372036854775808)) ; real sqlite so, i think, its 2^64-1 Luuk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select
On 10-12-10 11:53, lucavb wrote: Hello, i need to to do this: i have this table: userrates, composed by: id, userid, idtitle, rate. for example: (1, 12, 1, 3) (2, 15, 99, 4) (3, 22, 10, 1) (10, 22, 1, 5) (5, 166, 37, 1) (4, 244, 10, 2) (6, 298, 1, 4) (7, 298, 10, 3) (8, 298, 15, 2) i need to extract only the rows with the userid who had voted both fims (1 and 10): the result will be: (3, 22, 10, 1) (10, 22, 1, 5) (6, 298, 1, 4) (7, 298, 10, 3) How can i do that? Thanks in advance, Luca untested, something like: SELECT a.id, a.userid, a.idtitle, a.rate FROM userrates a INNER JOIN userrates b ON a.userid=b.userid AND b.idtitle=10 WHERE a.idtitle=1 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select
On 10-12-10 12:03, luuk34 wrote: On 10-12-10 11:53, lucavb wrote: Hello, i need to to do this: i have this table: userrates, composed by: id, userid, idtitle, rate. for example: (1, 12, 1, 3) (2, 15, 99, 4) (3, 22, 10, 1) (10, 22, 1, 5) (5, 166, 37, 1) (4, 244, 10, 2) (6, 298, 1, 4) (7, 298, 10, 3) (8, 298, 15, 2) i need to extract only the rows with the userid who had voted both fims (1 and 10): the result will be: (3, 22, 10, 1) (10, 22, 1, 5) (6, 298, 1, 4) (7, 298, 10, 3) How can i do that? Thanks in advance, Luca untested, something like: SELECT a.id, a.userid, a.idtitle, a.rate FROM userrates a INNER JOIN userrates b ON a.userid=b.userid AND b.idtitle=10 WHERE a.idtitle=1 oops... SELECT a.id, a.userid, a.idtitle, a.rate FROM userrates a INNER JOIN userrates b ON a.userid=b.userid AND b.idtitle=10 WHERE a.idtitle=1 UNION SELECT a.id, a.userid, a.idtitle, a.rate FROM userrates a INNER JOIN userrates b ON a.userid=b.userid AND b.idtitle=1 WHERE a.idtitle=10 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select
On 10-12-10 12:43, Kees Nuyt wrote: On Fri, 10 Dec 2010 02:53:32 -0800 (PST), lucavb bombhere...@gmail.com wrote: Hello, i need to to do this: i have this table: userrates, composed by: id, userid, idtitle, rate. for example: (1, 12, 1, 3) (2, 15, 99, 4) (3, 22, 10, 1) (10, 22, 1, 5) (5, 166, 37, 1) (4, 244, 10, 2) (6, 298, 1, 4) (7, 298, 10, 3) (8, 298, 15, 2) i need to extract only the rows with the userid who had voted both fims (1 and 10): the result will be: (3, 22, 10, 1) (10, 22, 1, 5) (6, 298, 1, 4) (7, 298, 10, 3) How can i do that? Assuming you mean idtitle where you write fims: SELECT a.id, a.userid, a.idtitle, a.rate FROM userrates AS a INNER JOIN ( SELECT userid FROM userrates WHERE idtitle IN (1,10) GROUP BY userid HAVING count(id) = 2 ) AS b ON b.userid = a.userid WHERE a.idtitle IN (1,10) ORDER BY a.userid,a.id; A user who has rated movie 1 twice and has not rated movie 10, would show up in your result.. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Documentation v2
On 10-12-10 19:02, Tito Ciuro wrote: Hello Dagdamor, Does it only work for Explorer? No Linux or Mac support? -- Tito *http://tinyurl.com/2vr3dal ^^ chm readers/viewers for linux... * ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [Bulk] How to optimize this simple select query ?
On 09-12-10 17:27, Vander Clock Stephane wrote: no one have an idea how to do such query ?? thanks stéphane On 12/8/2010 7:56 PM, Vander Clock Stephane wrote: Hello, on the table : CREATE TABLE HASH( . x5_y5=#randomnumber73 and x5_y5=#randomnumber73 + 20; because they takes very very lot of time (hourS) to return :( on other SGBD (like Firebird) with same amount of data they return immediatly ... If you have another situation, wit same amount of data, which returns immedialty, than either situation is not the same, or you are making an error. maybe creating indexes on more than one field, can speed up the query, but i cannot tell you on which fields, because i dont know the data ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reading a text file and insert to sqlite tables
On 09-12-10 18:13, yazdan asgari wrote: Hi I use C Programming Language and I also know that I could use INSERT command. But my problem is whether any one could show me a simple code which demonstrate how an INSERT command should be written during reading a text file. I have searched Google but I could not find any useful link. Yazdan u did not search correctly: http://www.google.com/search?hl=ensafe=offrlz=1G1ACAW_NLNL390q=sqlite+c++example+insert+databtnG=Searchaq=faqi=aql=oq=gs_rfai= 1st link points to: http://www.lemoda.net/sqlite/c-insert/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reading a text file and insert to sqlite tables
On 09-12-10 18:53, yazdan asgari wrote: --- On Thu, 12/9/10, luuk34luu...@gmail.com wrote: From: luuk34luu...@gmail.com Subject: Re: [sqlite] Reading a text file and insert to sqlite tables To: General Discussion of SQLite Databasesqlite-users@sqlite.org Date: Thursday, December 9, 2010, 9:21 AM On 09-12-10 18:13, yazdan asgari wrote: Hi I use C Programming Language and I also know that I could use INSERT command. But my problem is whether any one could show me a simple code which demonstrate how an INSERT command should be written during reading a text file. I have searched Google but I could not find any useful link. Yazdan u did not search correctly: http://www.google.com/search?hl=ensafe=offrlz=1G1ACAW_NLNL390q=sqlite+c++example+insert+databtnG=Searchaq=faqi=aql=oq=gs_rfai= 1st link points to: http://www.lemoda.net/sqlite/c-insert/ Hi Thanks for your quick reply. I saw the linked that you said but my problem is to INSERT data during reading a long text file in which every line consists of several queries. I could read this file and write to an output file but I am not able to write a sqlite command (INSERT in this case) in order to put data in desired tables. i fixed te top-quoot, because i think bottom-quoot is easier to read. But reading you question again, i dont seem to understand your problem... sorry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Documentation
On 04-12-10 14:15, Max Vlasov wrote: On Sat, Dec 4, 2010 at 11:51 AM, Dagdamordagdamo...@mail.ru wrote: Hello. Would you like to have a downloadable version of the SQLite Documentation? I've created one in Windows HTML Help (.chm) format. It seems to be useful, single-file (no need to zip/unzip it), compact and easy to navigate/search. I've uploaded a copy to my server: Serge, I don't know what's so special about my chm viewer (bundled with Windows 7), but I can only see the list of articles in the contents and in the search results on the left, the right pane always shows an error about canceling moving to a web-page. Max right click the file in explorer, and choose 'unblock' (on the general-tab) its there for 'safety'-purposes... ;-) you should, of course, only do this if you trust the docs... ___ 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] SQLite Documentation
On 04-12-10 17:30, Dagdamor wrote: luuk34luu...@gmail.com писал(а) в своём письме Sat, 04 Dec 2010 20:24:45 +0600: On 04-12-10 14:15, Max Vlasov wrote: Serge, I don't know what's so special about my chm viewer (bundled with Windows 7), but I can only see the list of articles in the contents and in the search results on the left, the right pane always shows an error about canceling moving to a web-page. Max right click the file in explorer, and choose 'unblock' (on the general-tab) its there for 'safety'-purposes... ;-) you should, of course, only do this if you trust the docs... Perhaps this is true, I'm still using win98, it's less restrictive :) The documents in the .chm are exact copies of the official documentation files - I haven't touched them, didn't even add missing/body and /html :P It should be safe to open. i should have added a link to microsoft, where they explain this: http://support.microsoft.com/kb/902225 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [ERROR] Failed compound query with simple expr in ORDER BY
On 26-11-10 12:37, Drake Wilson wrote: Quoth Waldemar Derrm...@wladid.de, on 2010-11-26 12:24:27 +0100: --Don't working: (Error: 1st ORDER BY term does not match any column in the result set.) SELECT * FROM OrderTest WHERE Price 200 UNION SELECT * FROM OrderTest WHERE Price 500 ORDER BY Price IS 0, Price; From http://sqlite.org/lang_select.html: | Otherwise, if the ORDER BY expression is any other expression, it is | evaluated and the the returned value used to order the output rows. If | the SELECT statement is a simple SELECT, then an ORDER BY may contain | any arbitrary expressions. However, if the SELECT is a compound | SELECT, then ORDER BY expressions that are not aliases to output | columns must be exactly the same as an expression used as an output | column. Is this a bug? In MySQL it works as expected. Is there a workaround? Add another output column with the expression you want, give it a name, and ORDER BY that name. My guess is that this is because the multiple output column specification parts of a compound SELECT may result in different intrepretations of an arbitrary expr in that position, and it's not clear how any kind of inwards propagation would work without yielding surprising results. --- Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users The extra column seems to work, but i thought this should work too? But the ORDER is wrong... sqlite SELECT a,b FROM ( ... SELECT ID a, Price b FROM OrderTest WHERE Price 200 ... UNION ... SELECT ID a, Price b FROM OrderTest WHERE Price 500 ... ) ... ORDER BY a IS 0, b; a|b 3|0.0 4|25.0 1|50.0 2|75.0 5|100.0 7|1000.0 8|1.0 sqlite ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [ERROR] Failed compound query with simple expr in ORDER BY
On 26-11-10 13:02, Drake Wilson wrote: Quoth luuk34luu...@gmail.com, on 2010-11-26 12:49:53 +0100: The extra column seems to work, but i thought this should work too? I would imagine so, at first glance. But the ORDER is wrong... How? The example you provided seems properly sorted. oeps, i made an error in the order by. so, indeed its working OK... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple SQL question?
On 17-11-10 00:17, Petite Abeille wrote: select t1.patient_id fromtable1 t1 join( select table1.address, min( table1.date_of_birth ) as date_of_birth fromtable1 group bytable1.address ) as t2 joint2.address = t1.address 'join' should be: ONt2.address = t1.address and t2.date_of_birth = t1.date_of_birth ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple SQL question?
On 17-11-10 09:58, Bart Smissaert wrote: What do you suggest should be the full SQL then? select t1.patient_id fromtable1 t1 join( select table1.address, min( table1.date_of_birth ) as date_of_birth fromtable1 group bytable1.address ) as t2 ON t2.address = t1.address and t2.date_of_birth = t1.date_of_birth ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple SQL question?
On 17-11-10 10:00, luuk34 wrote: On 17-11-10 09:58, Bart Smissaert wrote: What do you suggest should be the full SQL then? select t1.patient_id fromtable1 t1 join( select table1.address, min( table1.date_of_birth ) as date_of_birth fromtable1 group bytable1.address ) as t2 ON t2.address = t1.address and t2.date_of_birth = t1.date_of_birth i forgot the link: http://www.sqlite.org/syntaxdiagrams.html#join-constraint ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Key Value pairs in a table
On 17-11-10 10:32, Arigead wrote: Hi all, I've started to use an existing Database with C source code. As I'm new to all this I can't moan about database design but I'm sure that Key Value pairs in a Database table suits modern languages like Python down to the ground. It ain't suiting me though ;-) I'm really struggling to find a solution to my problems so I though I might try here. There are two database tables that I'm interested which keep track of contacts. One table contacts simply keeps contact_id which is unique A second table contact_name keeps track of key value pairs for the contacts: CREATE TABLE contacts_name (contacts_name_id INTEGER PRIMARY KEY,contacts_id REFERENCES contacts(contacts_id), field_name TEXT, value TEXT NOT NULL); CREATE INDEX contacts_name_contacts_id ON contacts_name(contacts_id); So in Contacts table I might have a few id's 1 2 And in contacts_name I might have a few key value pairs: ID field_namevalue 1 Name Tom 1 Surname Jones 2 Name Fred 2 Surname Flintstone I didn't design this system and it don't seem ideal to me, coming from C, but I'll have to get on with it. I decided that to make things simpler for my C I could create a temporary table and populate it with contact_id, name and Surname which is all that I'm interested in. So I created a new table with: create temporary table if not exists contacts_tmp (contacts_id REFERENCES contacts(contacts_id), Name TEXT, Surname TEXT) That's a database table I could work with ;-) Now I have to populate it with the data from the existing contacts_name table so I get the contacts_id and Name inserted with: INSERT INTO contacts_tmp (contacts_id, name) SELECT contacts_id, value FROM contacts_name WHERE field_name=Name The above statement works insofar as it populate id and name but I can't get surname into my table. I've tried to write a second insert statement to pull out the Surname tag from contacts_name but I just can't get it. I'm trying something along the lines of: INSERT INTO contacts_tmp (Surname) SELECT contacts_name.value FROM contacts_name where contacts_name.field_name=Surname JOIN contacts_name ON contacts_tmp.contacts_id = contacts_name.contacts_id Appologies for the long first post but I can't find a solution. Any advice, apart from redesigning the old tables would be greatefully received. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users select c.contacts_id, n.value as Name, s.value as Surname from contacts c left join contacts_name n on c.contacts_id=n.contacts_id and n.field_name='Name' left join contacts_name s on c.contacts_id=s.contacts_id and s.field_name='Surname' ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to optimize a multi-condition query
On 15-10-10 14:34, Black, Michael (IS) wrote: I love simple examples like this can help people with understanding things...so I tried this which I thought would do what Hilmar wants...but alaswhat concept am I missing? SQLite version 3.7.2 sqlite create table c(achr char,bchr char); sqlite create index c_chr on c(achr,bchr); sqlite explain query plan select achr,bchr from c where achr=bchr; 0|0|TABLE c Why no use of the index in this case? because there are no records in the database, so its quicker to read just all records, than to read all record in the order of the index... -- Luuk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tricky grouping query
On 01-10-10 22:30, Igor Tandetnik wrote:
Andy Chambersachambers.h...@gmail.com wrote:
Given the following
create table events (
id,
date,
status
);
insert into events values ('001','a','N');
insert into events values ('001','b','N');
insert into events values ('001','c','Y');
insert into events values ('001','d','N');
insert into events values ('001','e','Y');
insert into events values ('001','f','Y');
insert into events values ('001','g','N');
Is it possible, using plain SQL, to derive the following
001,c,d
001,e,g
i.e. an N in the third column means event 001 has stopped, and a
Y means it
has started back up again. Note that because the status immediately
preceding f
is also Y, there is no corresponding row in the output
select Start.id, Start.date, Stop.date
from events Start join events Stop on (Start.id = Stop.id and Start.date
Stop.date)
where Start.status = 'Y' and Stop.status = 'N' and not exists
(select 1 from events Middle
where Middle.date Start.date and Middle.date Stop.date and
Middle.status = 'N');
Igor Tandetnik
your query returns:
001|c|d
001|e|g
001|f|g
so i tried, and come up with this:
select e.id, e.date, a.date
from events e
left join events a on e.id=a.id and e.datea.date and a.status='N'
where (select status from events p where p.date e.date order by date
desc limit 1)='N' and e.status='Y'
and (select count(*) from events where date between e.date and a.date
and status='N')=1;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query to compare two sqlite databases
On 24-09-10 21:06, Rich Shepard wrote: On Fri, 24 Sep 2010, John Reed wrote: I compare an application every few days which has sqlite as it's client database. I look at the content and check whether documents have made it into the application after it has been built. I also check the metadata in the sqlite client database for changes. So, I am constantly comparing the last database with the newer database. Both databases have exactly the same tables, with only the data being changed in most of the 51 tables.The largest table has about 3,700,000 rows. Most other tables have much less rows in them. Could someone suggest an sql query to find the difference in the same table (ta) for both the last database (db1) and the newer database (db2)? I can use SQLiteSpy to connect and attach to the databases. You'll want to tune the syntax, but try something like: SELECT colA FROM db1 WHERE (NOT EXIST colA IN db2); The idea is to match rows in each table and where the equivalent row in db1 is not in db2, add that to the results table. Rich you mean something like: select id1, id2 from callprog a where id1 not in (select b.id1 FROM callprog b where b.id1=a.id1 ); But what is there is more than 1 column? it will grow in complexity when you have a lot of columns. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
