Sorry, i was too fast.
Correction
SELECT engine,coalesce(groupname,'*') as
groupname,databasename,key,value FROM EnginePreferences left join groups
on (groups.groupid = EnginePreferences.groupid);
OR
SELECT engine,groupname,databasename,key,value
FROM EnginePreferences
left join (selec
Hi,
SELECT engine,coalesce(groupname,*) as groupname,databasename,key,value
FROM EnginePreferences left join groups on (groups.groupid =
EnginePreferences.groupid);
OR
SELECT engine,coalesce(groupname,*) as groupname,databasename,key,value
FROM EnginePreferences
left join (select groupi
I have a table EnginePreference:
CREATE TABLE EnginePreferences (engine TEXT COLLATE NOCASE, databasename TEXT
COLLATE NOCASE, key TEXT COLLATE NOCASE, value TEXT, groupid INTEGER,
UNIQUE(engine,databasename,key))
and a table Groups:
CREATE TABLE Groups (groupid INTEGER PRIMARY KEY, groupname TE
> There are two ways to rewrite this query, with a correlated subquery:
>
> SELECT *
> FROM table_a AS x
> WHERE NOT EXISTS (SELECT 1
>FROM table_b AS y
>WHERE x.id = y.id
> AND x.col = y.col)
>
> or with an outer join:
>
> SELECT
Joseph L. Casale wrote:
> SELECT x.id, x.col
> FROM table_a x
> EXCEPT
> SELECT y.id, y.col
> FROM table_b y
> JOIN table_a .
This query is not complete, but as far as I can tell, it is intended to
return table_a rows that do not have a matching table_b row. Is this
correct?
> now I
I have a query I am trying to rewrite as efficient as possible and not clear.
SELECT x.id, x.col
FROM table_a x
EXCEPT
SELECT y.id, y.col
FROM table_b y
JOIN table_a .
The right hand part of the except performs several joins and already duplicates
the entire query on the left hand
> On 8 Jul 2014, at 9:01pm, Paul Sanderson wrote:
>
> 0, microsoft, mac
> 1, oracle, mac
> 2, oracle, pc
SELECT t1.recno, t2.name, t3.name FROM t1
JOIN t2 ON t2.a = t1.a
JOIN t3 ON t3.b = t1.b
ORDER BY t1.recno
If you have lots of data on your tables this command will execute far more
quic
On 7/8/2014 4:01 PM, Paul Sanderson wrote:
i have three tables
create table t1 (recno int, a int, b int)
create table t2 (a int, name text)
create table t3 (b int, name text)
I want to create a query that lists all rows in t1 but rather than the
integers a and b I want to display the associated
I suspect this is easy
i have three tables
create table t1 (recno int, a int, b int)
create table t2 (a int, name text)
create table t3 (b int, name text)
I want to create a query that lists all rows in t1 but rather than the
integers a and b I want to display the associated names from t2 and
> Will the SQL 1969 "EXCEPT" compound operator not work for some reason?
Worked perfect, my sql is weak as I didn't even know of this one...
Thanks!
jlc
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo
te-users@sqlite.org'
> Subject: [sqlite] Query help
>
> Hey guys,
> I am trying to left join the results of two selects that both look
> exactly like this:
>
> SELECT DISTINCT SUBSTR(col, INSTR(col, 'string')) AS name FROM
> table_a
>
> Both tables have
Hey guys,
I am trying to left join the results of two selects that both look exactly like
this:
SELECT DISTINCT SUBSTR(col, INSTR(col, 'string')) AS name FROM table_a
Both tables have the exact data type and format, I need to reformat each tables
results, then join and return only what is in t
tml e-mail
/\ www.asciiribbon.org
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Paul Sanderson
> Sent: Monday, 20 May, 2013 08:00
> To: General Discussion of SQLite Database
> Subject: [sqlite] query
On 5/20/2013 7:59 AM, Paul Sanderson wrote:
I have a table of the form
create table tab (num int1 unique, num2, int)
for each row for num2 there is usually a matching num1. But not always.
I want to identify each row where num2 does not have a matching num1
select * from tab where num2 not i
On Mon, May 20, 2013 at 12:59:45PM +0100, Paul Sanderson scratched on the wall:
> I have a table of the form
>
> create table tab (num int1 unique, num2, int)
>
> for each row for num2 there is usually a matching num1. But not always.
>
> I want to identify each row where num2 does not have a ma
== 0);
5
7
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paul Sanderson
Sent: Monday, May 20, 2013 7:00 AM
To: General Discussion of SQLite Database
Subject: [sqlite] query help
I have a table of the form
create table tab (num
I have a table of the form
create table tab (num int1 unique, num2, int)
for each row for num2 there is usually a matching num1. But not always.
I want to identify each row where num2 does not have a matching num1
example data might be
num1 num2
1 3
2 3
3 2
4
Hello,
Thanks for the help.
On Sun, Mar 10, 2013 at 10:36 PM, Igor Tandetnik wrote:
> On 3/10/2013 11:06 AM, Navaneeth.K.N wrote:
>>
>> select distinct(lower(pattern)) as pattern, id from symbols where
>> value1 = ?1 or value2 = ?1 group by pattern
>>
>> This returns
>>
>> "cchu", "20907"
>>
On Sun, 10 Mar 2013 20:36:47 +0530
"Navaneeth.K.N" wrote:
> select distinct(lower(pattern)) as pattern, id from symbols where
> value1 = ?1 or value2 = ?1 group by pattern
Igor's answer is correct. I just want to point out what looks like a
misunderstanding on your part regarding "distinct"
On 3/10/2013 11:06 AM, Navaneeth.K.N wrote:
select distinct(lower(pattern)) as pattern, id from symbols where
value1 = ?1 or value2 = ?1 group by pattern
This returns
"cchu", "20907"
"chchu", "20879"
"chu", "20935"
This is distinct set of patterns, but I am not getting the list
ordered by i
Hi Guys,
I have a table named "symbols". I am writing the below query.
select lower(pattern), id from symbols where value1 = ?1 or value2 = ?1
This returned the following results.
"chu", "20851"
"chchu", "20879"
"cchu", "20907"
"chu", "20935"
>From this, I need only distinct patterns. So I t
On Wed, Apr 18, 2012 at 6:38 PM, Jim Sanders wrote:
>
> It's disappointing there isn't a simple "unhex()" function to do the
> reverse of the already existing function.
>
Code to implement such a function is contained in the SQLite test suite.
See it here:
http://www.sqlite.org/src/artifact/6
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 18/04/12 15:38, Jim Sanders wrote:
> It's disappointing there isn't a simple "unhex()" function to do the
> reverse of the already existing function. That would be a very
> elegant solution to this issue, as I am so close.
unhex isn't anywhere n
Well yes I agree the stored format isn't ideal, but unfortunately I have no
control over that. These are hard drive serial numbers and that is the
format that Microsoft WMI spits them out in. It's possible that the
Spiceworks application could be modified to decode those hex strings it
gets bac
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 18/04/12 14:39, Jim Sanders wrote:
> I found that, but I need essentially the reverse of that. The data
> stored in SQL is 3061626364 and I need to convert it back to 0abcd
> (from your example)
The correct fix is to get them to stop storing stri
I found that, but I need essentially the reverse of that. The data stored
in SQL is 3061626364 and I need to convert it back to 0abcd (from your
example)
On Wed, Apr 18, 2012 at 5:35 PM, Roger Binns wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 18/04/12 14:30, Jim Sanders wro
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 18/04/12 14:30, Jim Sanders wrote:
> But I can't figure out a way do this dynamically for all rows.
There is a builtin hex() function.
sqlite> create table foo(bar); insert into foo values('0abcd');
sqlite> select hex(bar) from foo;
30616263
I am trying to write a query to a SQLite database (Spiceworks) in which I
need to transform some data as part of the SELECT statement.
The query I *want* to use looks like this (includes some pseudo code):
SELECT d.name, d.current_user, d.device_type, d.model, d.operating_system,
d.service_pack
Thanks a lot Igor, it's perfect now.
--
Marco Bambini
http://www.sqlabs.com
On Jun 12, 2011, at 4:46 PM, Igor Tandetnik wrote:
> Marco Bambini wrote:
>> I have a table Clients defined as (simplified version):
>> CREATE TABLE Clients (id INTEGER PRIMARY KEY AUTOINCREMENT, last_activity
>
Marco Bambini wrote:
> I have a table Clients defined as (simplified version):
> CREATE TABLE Clients (id INTEGER PRIMARY KEY AUTOINCREMENT, last_activity
> TEXT, ping_timeout INTEGR);
>
> ping_timeout was a global property so in order to get a list of all clients
> timedout I used a query like
No I am sorry but I need to query the ping_timeout column from inside the same
query.
--
Marco Bambini
http://www.sqlabs.com
On Jun 12, 2011, at 2:02 PM, Roger Andersson wrote:
> On 06/12/11 01:52 PM, Marco Bambini wrote:
>> things are recently changed in my app and ping_timeout is now
On 06/12/11 01:52 PM, Marco Bambini wrote:
> things are recently changed in my app and ping_timeout is now a client
> property set inside the Clients table (and no longer a global property), so I
> would like to perform the query:
> snprintf(sql, sizeof(sql), "select id from Clients where last_
Hello guys,
I have a table Clients defined as (simplified version):
CREATE TABLE Clients (id INTEGER PRIMARY KEY AUTOINCREMENT, last_activity TEXT,
ping_timeout INTEGR);
each time a client performs an operation the last_activity column is updated
with:
UPDATE Clients SET last_activity=datetime(
On 1 Mar 2011, at 22:01, Jeff Archer wrote:
> Hi all, and thanks in advance for you help.
>
> And this select which I would like to modify to only return lowest X,Y value
> for each ImageID.
>
> select Defects.DefectID
> , Defects.ImageID
> , Defects.AnalysisID
> , Defects.X
> , Defects.Y
> , De
On 3/1/2011 6:47 PM, Jeff Archer wrote:
> I think it will just happen to work out if I could get the first row
> for each ImageID since the values should have been entered in
> ascending order. I realize this will probably not be guaranteed to
> get lowest X,Y but for my purpose at the moment this
>From: Igor Tandetnik [mailto:itandet...@mvps.org]
>Sent: Tuesday, March 01, 2011 5:47 PM
>
>On 3/1/2011 5:01 PM, Jeff Archer wrote:
>> And this select which I would like to modify to only return lowest X,Y
>> value for each ImageID.
>
>What does "lowest" mean? If you have two points (100, 200) a
On 3/1/2011 5:01 PM, Jeff Archer wrote:
> And this select which I would like to modify to only return lowest X,Y value
> for each ImageID.
What does "lowest" mean? If you have two points (100, 200) and (200,
100), which one is "lower"?
--
Igor Tandetnik
_
Hi all, and thanks in advance for you help.
I have the following schema
CREATE TABLE [Scans]
(ScanIDINTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
,Timestamp DATETIME NOT NULL UNIQUE
,EndTime DATETIME NOT NULL DEFAULT CURRENT_TIME
,ResultVARCHAR
);
CREATE TAB
Thanks Igor and thanks Martin,
I need to add both the id and the other properties to an hash table (a Cocoa
NSDictionary) so I needed a way to have a key, value representation that
includes also the id.
I solved the problem with 2 queries and some Cocoa code.
I don't like complex queries and 2 s
On 2/2/2011 11:16 AM, Marco Bambini wrote:
> your query returns 3 columns, but I need just two columns (key, value for
> example).
Why? You have all the information you need, just in a slightly different
(and, arguably, easier to use) form.
> The first row should be the label 'ID' and the id of
Hello Igor,
your query returns 3 columns, but I need just two columns (key, value for
example).
The first row should be the label 'ID' and the id of the MKObjects followed by
a SELECT prop_key, prop_value WHERE obj_id= MKObjects.id.
For example MKObjects contains (1,IPHONE,PANEL,0)
and MKProper
Hello Marco,
As far as i can see, the union is necessary. However, the second select
in the union can be rewritten as a join:
SELECT 'ID', id
FROM MKObjects
WHERE type='PANEL' AND platform='IPHONE'
UNION
SELECT prop_key, prop_value
FROM MKProperties
JOIN MKObjects on MKProperties.
Marco Bambini wrote:
> Hello, I have two tables defined as:
>
> CREATE TABLE MKObjects (id INTEGER PRIMARY KEY AUTOINCREMENT, platform TEXT,
> type TEXT, parent_id INTEGER DEFAULT 0);
> CREATE TABLE MKProperties (id INTEGER PRIMARY KEY AUTOINCREMENT, obj_id
> INTEGER, prop_key TEXT, prop_value
Hello, I have two tables defined as:
CREATE TABLE MKObjects (id INTEGER PRIMARY KEY AUTOINCREMENT, platform TEXT,
type TEXT, parent_id INTEGER DEFAULT 0);
CREATE TABLE MKProperties (id INTEGER PRIMARY KEY AUTOINCREMENT, obj_id
INTEGER, prop_key TEXT, prop_value TEXT, UNIQUE(obj_id, prop_key));
qlite-users-boun...@sqlite.org on behalf of Ian Hardingham
Sent: Tue 11/16/2010 7:31 AM
To: General Discussion of SQLite Database
Subject: EXTERNAL:Re: [sqlite] Query help - two similar queries
Many thanks again Igor.
On 16/11/2010 13:15, Igor Tandetnik wrote:
> Ian Hardingham wrote:
>>
Many thanks again Igor.
On 16/11/2010 13:15, Igor Tandetnik wrote:
> Ian Hardingham wrote:
>> Thanks Igor. Can i get custom results like
>>
>> GamesWonByPlayer1
>>
>> By using getColumn in the normal way?
> I'm not familiar with the term "custom result". GamesWonByPlayer1 is just a
> column al
Ian Hardingham wrote:
> Thanks Igor. Can i get custom results like
>
> GamesWonByPlayer1
>
> By using getColumn in the normal way?
I'm not familiar with the term "custom result". GamesWonByPlayer1 is just a
column alias which a) is completely optional, you could safely drop it from the
query
Thanks Igor. Can i get custom results like
GamesWonByPlayer1
By using getColumn in the normal way?
That may be a stupid question - I guess what I mean is, are those custom
identifiers treated as column names when reading back from the select?
Thanks,
Ian
On 16/11/2010 13:04, Igor Tandetnik w
Ian Hardingham wrote:
> I have a badly designed structure for a table which records /games
> played/ by people. It looks like:
>
> id
> player1
> player2
> score
>
> If score > 0, player 1 won the game. If score < 0, player 2 won it.
> (Score of 0 is a draw).
>
> I wish to find the total reco
Hey guys.
I have a badly designed structure for a table which records /games
played/ by people. It looks like:
id
player1
player2
score
If score > 0, player 1 won the game. If score < 0, player 2 won it.
(Score of 0 is a draw).
I wish to find the total record in games between two specific
Thank You.
On 26 October 2010 13:14, Igor Tandetnik wrote:
> Paul Sanderson wrote:
>> I have two tables, table b is a subset of table a. both tables have
>> the same primary key
>>
>> I want to update the rows from table a with a single column from table
>> b, what sql command would be most effi
Paul Sanderson wrote:
> I have two tables, table b is a subset of table a. both tables have
> the same primary key
>
> I want to update the rows from table a with a single column from table
> b, what sql command would be most efficient for this?
update a set ColumnToUpdate = coalesce(
(selec
I have two tables, table b is a subset of table a. both tables have
the same primary key
I want to update the rows from table a with a single column from table
b, what sql command would be most efficient for this?
Thanks
___
sqlite-users mailing list
sq
On 19 October 2010 16:26, jeff archer wrote:
> I have a table containing width and height of images with columns wPixels,
> hPixels. I would like to select all rows that have either a unique wPixels
> or a
> unique hPixels value.
>
> for this data:
> 10, 20
> 10, 20
> 10, 30
> 10, 3015, 10
> 15,
I have a table containing width and height of images with columns wPixels,
hPixels. I would like to select all rows that have either a unique wPixels or
a
unique hPixels value.
for this data:
10, 20
10, 20
10, 30
10, 3015, 10
15, 30
15, 30
15, 30
I would like to select:
10, 20
10, 30
15, 10
1
qlite-users@sqlite.org
Subject: EXTERNAL:Re: [sqlite] EXTERNAL: SQLite query help pls
Hi,
Well i have to say i am like a kid in a sweet shop right now, you all may
have just saved me 6 or so hours work. Thanks again for your input.
I was wondering if anyone had any issues with me posting this
people that it came from here.
Thanks again,
Rich
--
View this message in context:
http://old.nabble.com/SQLite-query-help-pls-tp29983175p2989.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users
Dickie.wild wrote:
> I thought that looked like it would get the same results, but i seem to be
> getting the following error, are you able to try it and let me know if your
> getting the same error?
>
> SQLiteManager: Likely SQL syntax error: UPDATE video_files SET strCover TO
> (rtrim(strPath,r
sqlite.org on behalf of Dickie.wild
Sent: Tue 10/19/2010 7:35 AM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:Re: [sqlite] EXTERNAL: SQLite query help pls
Hi,
I thought that looked like it would get the same results, but i seem to be
getting the following error, are you able to try it and let
,'\','')) || 'folder.jpg') [ near "TO":
syntax error ]
Exception Name: NS_ERROR_FAILURE
Exception Message: Component returned failure code: 0x80004005
(NS_ERROR_FAILURE) [mozIStorageConnection.createStatement]
--
View this message in context:
http://old.nabble.com/S
op Grumman Information Systems
From: sqlite-users-boun...@sqlite.org on behalf of Simon Slavin
Sent: Tue 10/19/2010 7:17 AM
To: General Discussion of SQLite Database
Subject: EXTERNAL:Re: [sqlite] EXTERNAL: SQLite query help pls
On 19 Oct 2010, at 1:07pm, Di
On 19 Oct 2010, at 1:07pm, Dickie.wild wrote:
> Thanks for the reply's i have tried the various ways described that they do
> not seem to be working. I have a way in which it works but this is in SQL
> could anyone convert it to SQLite for me? i am not to sure it is even
> possible. I have also a
t for me?
Update video_files
Set strCover =
Reverse(Substring(Reverse(strPath),CHARINDEX('\',Reverse(strPath)),1000)) +
'Folder.jpg'
http://old.nabble.com/file/p2497/Boxee_catalog.db Boxee_catalog.db
--
View this message in context:
http://old.nabble.com/SQLite-query-hel
;d share it.
select RTRIM(path,REPLACE(path,'\','')) from
(select 'C:\richEminem\file.txt' path)
--
View this message in context:
http://old.nabble.com/SQLite-query-help-pls-tp29983175p29985874.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
un...@sqlite.org on behalf of Dickie.wild
Sent: Sun 10/17/2010 7:26 AM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:[sqlite] SQLite query help pls
Hi All,
I was hoping for a little help, well little would be an understatement, I
currently have a file location in a field and i would like to take all
On 17 Oct 2010, at 1:26pm, Dickie.wild wrote:
> I was hoping for a little help, well little would be an understatement, I
> currently have a file location in a field and i would like to take all of it
> up untill the last / (folder) and copy it in to another column and then
> attach folder.jpg on
You want to strip a complete path + name and save them as separate
fields, or you already have it splitted and want to join them together ?
On 10/17/2010 09:26 AM, Dickie.wild wrote:
>
> Hi All,
>
> I was hoping for a little help, well little would be an understatement, I
> currently have a file
something like
c:\rich\Eminem\folder.jpg
I have never used SQLite before and help would be great
R
--
View this message in context:
http://old.nabble.com/SQLite-query-help-pls-tp29983175p29983175.html
Sent from the SQLite mailing list archive at Nabble.com
"Marco Bambini" wrote
in message news:3265458b-af7b-434f-83e8-f9448bab0...@sqlabs.net
> Hello all,
>
> I have a table foo (id INTEGER, tid INTEGER, optype INTEGER),
> and I have some data into foo:
> id id2 optype
> -
> 1 2 10
> 2 2 10
> 3 2 10
> 4 2 10
> 5 2 10
> 6 2 20
> 7 2
Hello all,
I have a table foo (id INTEGER, tid INTEGER, optype INTEGER),
and I have some data into foo:
id id2 optype
-
1 2 10
2 2 10
3 2 10
4 2 10
5 2 10
6 2 20
7 2 10
8 2
What if you took a slightly different tack?
CREATE TABLE FinishedWork (
EventTime INTEGER NOT NULL,
FileName TEXT NOT NULL,
ProcessID INTEGER NOT NULL,
BytesProcessed INTEGER NOT NULL,
isDuplicate integer-- tri-state: 0=not duplicate 1=duplicate
null=unknown
);
And then periodically run this
"Doug" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Thank you Igor. The GROUP BY was the secret (I was trying to do a
> GROUP BY on the outer select, but it wasn't quite cutting it).
>
> GROUP BY is very powerful, but I notice it has a performance cost.
> Is there a way to use an
f Of Igor Tandetnik
> Sent: Tuesday, May 27, 2008 12:24 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Query help?
>
> Doug <[EMAIL PROTECTED]> wrote:
> > SELECT ProcessID, count(1), sum(BytesProcessed)
> > FROM FinishedWork
> > WHERE Eve
Doug <[EMAIL PROTECTED]> wrote:
> SELECT ProcessID, count(1), sum(BytesProcessed)
> FROM FinishedWork
> WHERE EventTime > {20 minutes ago}
> GROUP BY ProcessID, FileName
>
> Unfortunately when a file is processed twice, it's counted twice (ie
> added into the sum twice) and I need to show only uniq
Hi,
I Have a problem with the following query executed on sqlite
SELECT CUSTOMER,
PRODUCT,
[RANK]
FROM (SELECT CUSTOMER,
PRODUCT,
[SUM_SALES],
(SELECT COUNT(T2.SUM_SALES)
FROM (SELECT CUSTOMER,
75 matches
Mail list logo
