Re: [sqlite] Help with SQL and index (or schema?)

On Fri, 27 Mar 2009 18:08:13 +0100, Kees Nuyt wrote: > PRIMARY KEY (filepathid,filename,istarget) Oops, make that PRIMARY KEY (pathid,filename,istarget) -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list

Re: [sqlite] Help with SQL and index (or schema?)

On Fri, 27 Mar 2009 15:53:18 +0100, Jonas Sandman wrote: >Hello, > >I have a database with about 137000 * 2 rows with four columns; >fileid, filename, filepath and istarget. >It's used to determine if two scanned directories are equal so I run a >simply query to get the

Re: [sqlite] help with SQL

It's not liking the "as" ... this works more or less.. select b.amountowed, b.apr, b.dueday, b.minimumdue, b.payee, b.payeeid, b.iscc, b.isactive, p2.payeeid, p2.amount, case p2.amount when '' then 0 else sum(amount) end as amount, case p2.payments when null then 0 else 1 end as paid, payments

[sqlite] help with SQL

I have this SQL in Postgres and it works fine, I'm trying to port it over to SQLite and having issues.. SQLite doesnt like left outer joining to a subquery.. Can you guys think of a way around this? select b.amountowed, b.apr, b.dueday, b.minimumdue, b.payee, p.note, b.payeeid, case when p.amount

Re: [sqlite] Help on sql syntax, left join and group_concat

"He Shiming" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] >> select NAME, group_concat(COUNT), group_concat(TYPE) >> from ( >> select T1.NAME NAME, T2.COUNT COUNT, T2.TYPE TYPE >> from T1 left join T2 on T1.ID=T2.REFID >> order by T2.COUNT); >> > Thanks for the hint. It works,

Re: [sqlite] Help on sql syntax, left join and group_concat

> First, note that left join is a red herring here, since you don't in fact > have any records in T1 without a matching record in T2. > > Try this: > > select NAME, group_concat(COUNT), group_concat(TYPE) > from ( > select T1.NAME NAME, T2.COUNT COUNT, T2.TYPE TYPE > from T1 left join T2 on

Re: [sqlite] Help on sql syntax, left join and group_concat

"He Shiming" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I need some help on a particular sql statement syntax. Consider the > following tables: > > T1: > ID, NAME > 1, John > > T2: > REFID, COUNT, TYPE > 1, 9, B > 1, 5, U > 1, 8, T > > I have the following statement: > select

[sqlite] Help on sql syntax, left join and group_concat

Hi, I need some help on a particular sql statement syntax. Consider the following tables: T1: ID, NAME 1, John T2: REFID, COUNT, TYPE 1, 9, B 1, 5, U 1, 8, T I have the following statement: select T1.NAME, group_concat(T2.COUNT), group_concat(T2.TYPE) from T1 left join T2 on T1.ID=T2.REFID;

[sqlite] help with sql query / command SUM

Hi folks! I Have two tables, and i need access data from both. below it's my tables; CREATE TABLE Product ( CD_PROD INTEGER CONSTRAINT PK_PROD NOT NULL PRIMARY KEY AUTOINCREMENT, CD_REMT_COMM INTEGER CONSTRAINT FK_PROD_REMT_COMM REFERENCES Remote_command(CD_REMT_COMM),

RE: [sqlite] Help wiith SQL - first row of each group

e 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

Re: [sqlite] Help wiith SQL - first row of each group

On Mon, May 07, 2007 at 19:20:49 +0400, Tomash Brechko wrote: > But with SQLite 3.x there is only one user data pointer per user > defined aggregate function, so no parallel aggregate execution is > possible. Ah, I was too fast again :/. GROUP BY is really two-pass, but my understanding why it

Re: [sqlite] Help wiith SQL - first row of each group

On Mon, May 07, 2007 at 18:10:29 +0400, Tomash Brechko wrote: > The solution with two table scans might be > > SELECT * FROM RESULT_TABLE a > WHERE P = (SELECT MIN(P) FROM RESULT_TABLE b WHERE b.G = a.G); > > You may also try to play with indexes G, P. > > It seems to be impossible to do

RE: [sqlite] Help wiith SQL - first row of each group

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 ( G

Re: [sqlite] Help wiith SQL - first row of each group

On Mon, May 07, 2007 at 15:38:22 +0300, Adler, Eliedaat wrote: > I need to define a query that returns only the "first' row in each group > - i.e. the row with the lowest display priority: > > - most preferably a query that doesn't require selecting RESULT_TABLE > more than once. The solution

Re: [sqlite] Help wiith SQL - first row of each group

=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 r

[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

Re: [sqlite] Help with SQL syntax. Ticket #2296

Samuel R. Neff wrote: Still, I think backwards compatibility and consistency with other databases would be most important in this situation. I just checked MSSQL and it's same as current sqlite which uses the first select statement's column names. Samuel, The following is from Oracle's

RE: [sqlite] Help with SQL syntax. Ticket #2296

PROTECTED] Subject: Re: [sqlite] Help with SQL syntax. Ticket #2296 On 4/12/07, Samuel R. Neff <[EMAIL PROTECTED]> wrote: > > > Wouldn't implementation dependent mean it's not really standardized? The > way I read it the query could still be considered legal in some dbms and

Re: [sqlite] Help with SQL syntax. Ticket #2296

--- [EMAIL PROTECTED] wrote: > Consider this query: > >SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY a,b; > > Is the query above equalent to: > > (1) SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY 1,2; > > Or is it the same as: > > (2) SELECT a, b FROM t1 UNION

Re: [sqlite] Help with SQL syntax. Ticket #2296

On 4/12/07, Samuel R. Neff <[EMAIL PROTECTED]> wrote: Wouldn't implementation dependent mean it's not really standardized? The way I read it the query could still be considered legal in some dbms and not in others (which stinks). Samuel, That's not what the standard says. It says the name

RE: [sqlite] Help with SQL syntax. Ticket #2296

s Cote [mailto:[EMAIL PROTECTED] Sent: Thursday, April 12, 2007 6:05 PM To: [EMAIL PROTECTED] Subject: Re: [sqlite] Help with SQL syntax. Ticket #2296 ... Otherwise, the of the i-th column of TR is implementation dependent and not equivalent to the of any column, other than itself, of any ta

Re: [sqlite] Help with SQL syntax. Ticket #2296

At 7:22 PM + 4/12/07, [EMAIL PROTECTED] wrote: Consider this query: SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY a,b; Is the query above equalent to: (1) SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY 1,2; Or is it the same as: (2) SELECT a, b FROM t1

Re: [sqlite] Help with SQL syntax. Ticket #2296

[EMAIL PROTECTED] wrote: Consider this query: SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY a,b; Is the query above equalent to: (1) SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY 1,2; Or is it the same as: (2) SELECT a, b FROM t1 UNION SELECT b, a FROM t1

RE: [sqlite] Help with SQL syntax. Ticket #2296

. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Andrew Finkenstadt [mailto:[EMAIL PROTECTED] Sent: Thursday, April 12, 2007 3:42 PM To: [EMAIL PROTECTED] Subject: Re: [sqlite] Help with SQL syntax. Ticket #2296 My understanding is: select a, b from t1 union

Re: [sqlite] Help with SQL syntax. Ticket #2296

My understanding is: select a, b from t1 union select b, a from t1 is equivalent to select a as a, b as b from t1 union select b as a, a as b from t1 And therefore, the first sql statement controls the resulting column names, and the order by applies to the column names (transitively)

[sqlite] Help with SQL syntax. Ticket #2296

Consider this query: SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY a,b; Is the query above equalent to: (1) SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY 1,2; Or is it the same as: (2) SELECT a, b FROM t1 UNION SELECT b, a FROM t1 ORDER BY 2,1; I need to know

RE: [sqlite] Help with "SQL error: database is locked"

Hi, I am in the process of porting sqlite (version 2.8.13) to vxworks. I am running into some problems. It seems to work when I invoke the shell with no database (:memory:). I can then create tables, insert records, etc. In general I can submit SQL queries at will. However, when I invoke the