[sqlite] sql query

On Thu, 10 Sep 2015 13:17:03 -0400 H?ctor Fiandor wrote: > Dear members: > > I am trying to use a SQL statement like this: > > fdm.tNegSerAct.SQL:='SELECT * FROM NegSerAct > > WHERE codNegSerAct >=desde AND codNegSerAct <=hasta > > ORDER BY codNegSerAct'; > but the trouble is that

[sqlite] sql query

Hi, On Thu, Sep 10, 2015 at 1:17 PM, H?ctor Fiandor wrote: > Dear members: > > I am trying to use a SQL statement like this: > > fdm.tNegSerAct.SQL:='SELECT * FROM NegSerAct > > WHERE codNegSerAct >=desde AND codNegSerAct <=hasta > > ORDER BY codNegSerAct'; > > > > but the trouble is

[sqlite] sql query

Dear members: I am trying to use a SQL statement like this: fdm.tNegSerAct.SQL:='SELECT * FROM NegSerAct WHERE codNegSerAct >=desde AND codNegSerAct <=hasta ORDER BY codNegSerAct'; but the trouble is that the variables ?desde? and ?hasta? are strings and fixed previously.

Re: [sqlite] [SQL Query] Top 10 of 5 different groups sorted by length

On 10/14/2014 3:51 AM, Mark Lawrence wrote: On Tue Oct 14, 2014 at 09:25:20AM +0200, RSmith wrote: To get even more compact, I would go with Igor's SQL which ... will run quite a bit slower I'm a little curious about why you say a CTE statement is slower than a VIEW for large tables. My

Re: [sqlite] [SQL Query] Top 10 of 5 different groups sorted bylength

I forgot to also factor out the ORDER BY. So, the updated query is: - with t as ( select GroupName, JobName, Start, End, Status, (strftime('%s', End) - strftime('%s', Start)) as Length from ReportJobs where PlanDate = '2014-02-13' order by Length desc )

Re: [sqlite] [SQL Query] Top 10 of 5 different groups sorted by length

On Tue Oct 14, 2014 at 09:25:20AM +0200, RSmith wrote: > > > >To get even more compact, I would go with Igor's SQL which is > >quite succint, but if those tables are big, that query will run > >quite a bit slower - which is only a problem if the speed really > >matters. I'm a little curious about

Re: [sqlite] [SQL Query] Top 10 of 5 different groups sorted by length

And of course there must be a LIMIT 10 added to every Union'd select (which I forgot): SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP01%' LIMIT 10 UNION ALL SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP04%' LIMIT 10 UNION ALL SELECT * FROM

Re: [sqlite] [SQL Query] Top 10 of 5 different groups sorted by length

On 2014/10/13 23:21, pihu...@free.fr wrote: Hello! I'm trying to find a way to reduce the length of the following query using SQLite: select * from (select GroupName, JobName, Start, End, Status, (strftime('%s', End) - strftime('%s', Start)) as Length from ReportJobs where PlanDate =

Re: [sqlite] [SQL Query] Top 10 of 5 different groups sorted by length

To: sqlite-users@sqlite.org Subject: [sqlite] [SQL Query] Top 10 of 5 different groups sorted by length Hello! I'm trying to find a way to reduce the length of the following query using SQLite: select * from (select GroupName, JobName, Start, End, Status, (strftime('%s', End) - strftime('%s', Start

Re: [sqlite] [SQL Query] Top 10 of 5 different groups sorted by length

On 10/13/2014 5:21 PM, pihu...@free.fr wrote: Do you know a simplest/better way to perform this query? Something along these lines: select * from ReportJobs r1 where rowid in ( select r2.rowid from ReportJobs r2 where substr(r2.GroupName, 1, 5) = substr(r1.GroupName, 1, 5) and

[sqlite] [SQL Query] Top 10 of 5 different groups sorted by length

Hello! I'm trying to find a way to reduce the length of the following query using SQLite: select * from (select GroupName, JobName, Start, End, Status, (strftime('%s', End) - strftime('%s', Start)) as Length from ReportJobs where PlanDate = '2014-02-13' and GroupName like 'GRP01%' ORDER BY

Re: [sqlite] SQL Query to Vdbe Instructions

for explain). If you just want to know what the optimizer was thinking, use: explain query plan ; -Ursprüngliche Nachricht- Von: Prakash Premkumar [mailto:prakash.p...@gmail.com] Gesendet: Dienstag, 16. September 2014 07:53 An: sqlite-users@sqlite.org Betreff: [sqlite] SQL Query to Vdbe

[sqlite] SQL Query to Vdbe Instructions

Hi, Can you please tell me which function/set of functions convert the SQL query to Vdbe program ? Thanks Prakash ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL query

f Paul Sanderson Sent: Thursday, January 31, 2013 5:08 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQL query Cool that seems to work - thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/li

Re: [sqlite] SQL query

Subject: Re: [sqlite] SQL query Still playing with this I have the following table and I run the following query - the results of which are what I expect name, num, md5 sqlite> select * from rtable; $RmMetadata|0|8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279 $RmMetadata|1|8465-CEEF-126A-0F04-1EDC-1

Re: [sqlite] SQL query

Still playing with this I have the following table and I run the following query - the results of which are what I expect name, num, md5 sqlite> select * from rtable; $RmMetadata|0|8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279 $RmMetadata|1|8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279

Re: [sqlite] SQL query

D4-A427-9FE2-9724-BF95-1571-7CE5 -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paul Sanderson Sent: Wednesday, January 30, 2013 4:33 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQL query Thanks - replace

Re: [sqlite] SQL query

t; SELECT * FROM files WHERE hash NOT IN (SELECT hash FROM files WHERE > setid=0); > 1.jpg|4|890B-4533-447E-6461-070E-FDB7-799E-1FB8 > > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paul Sanderson >

Re: [sqlite] SQL query

--- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paul Sanderson Sent: Wednesday, January 30, 2013 4:05 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQL query I have a test set with the following real data 1.jpg05DA4-CD3A-62DE-2F9D-4B

Re: [sqlite] SQL query

I have a test set with the following real data 1.jpg05DA4-CD3A-62DE-2F9D-4BD7-6E24-EACE-936D 1.jpg15DA4-CD3A-62DE-2F9D-4BD7-6E24-EACE-936D 1.jpg25DA4-CD3A-62DE-2F9D-4BD7-6E24-EACE-936D 1.jpg35DA4-CD3A-62DE-2F9D-4BD7-6E24-EACE-936D 1.jpg4

Re: [sqlite] SQL query

ref not in (select ref from t where num=0); EF01 EE34 FF34 -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paul Sanderson Sent: Wednesday, January 30, 2013 3:37 PM To: General Discussion of SQLite Database Subject: Re: [sqlite

Re: [sqlite] SQL query

Thanks All - duplicated means the content is the same as well as the name, different is the filename is the same but the content is different. I need to refine my query to produce only one copy of any that is not in set 0 file10ABCD file11ABCD file13EF01 file20

Re: [sqlite] SQL query

> I have a database with many million rows with in it each representing a > file. There are many duplicate files in the database and all files are > hashed. > > The files are sub categorised into a number of sets, numbered 0 to 10 for > example. Files do not need to be in every set. > > I need

Re: [sqlite] SQL query

On Wed, Jan 23, 2013 at 12:17 PM, Paul Sanderson < sandersonforens...@gmail.com> wrote: > I have a database with many million rows with in it each representing a > file. There are many duplicate files in the database and all files are > hashed. > > The files are sub categorised into a number of

Re: [sqlite] SQL query

-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paul Sanderson Sent: Wednesday, January 23, 2013 12:18 PM To: General Discussion of SQLite Database Subject: [sqlite] SQL query I have a database with many million rows

Re: [sqlite] SQL Query - finding rows in a table that are not present in another

On May 23, 2012, at 11:12 AM, Paul Sanderson wrote: > I need to return all of the rows in table B that are not present in table A You have at least 3 ways to express such a query: (1) Using 'in' select table_b.* fromtable_b where table_b.key not in ( select key from table_a ) (2)

[sqlite] SQL Query - finding rows in a table that are not present in another

I have a couple of table seach of which has one column but millions of rows, the column is a text column. I need to return all of the rows in table B that are not present in table A What is the most efficient way of doing this? ___ sqlite-users mailing

Re: [sqlite] SQL query causes various hangs/crashes/exits

One of the things I try to maintain when writing databases is that whenever I start poking at a field with WHERE, said field gets an index to its own, and then I leave it to the engine of choice (SQLite, MSSql, MySQL) to decide to use the indexes. I have rarely been let down performance wise.

Re: [sqlite] SQL query causes various hangs/crashes/exits

Hello... On 27/1/2012 3:20 AM, Larry Knibb wrote: On 25 January 2012 21:01, Igor Tandetnik wrote: This index can help satisfy conditions of the form (traditional='X') or (traditional='X' AND simplified='Y'). But it doesn't help at all for conditions on (simplified='Y')

Re: [sqlite] SQL query causes various hangs/crashes/exits

Thanks! Splitting the index into two did the trick and now the query is working well. Cheers, Larry On 25 January 2012 21:01, Igor Tandetnik wrote: > Larry Knibb wrote: >> SELECT DISTINCT d.rowid AS id, d.*, i.relevance >> FROM dictionary d >> JOIN

Re: [sqlite] SQL query causes various hangs/crashes/exits

On 25 Jan 2012, at 1:01pm, Igor Tandetnik wrote: > So your query devolves to a full table scan, and apparently, that just takes > a long time. That's my guess: the query is taking a long time. It's taking long enough that your database infrastructure (which seems to be SQLite Database

Re: [sqlite] SQL query causes various hangs/crashes/exits

Larry Knibb wrote: > SELECT DISTINCT d.rowid AS id, d.*, i.relevance > FROM dictionary d > JOIN hp_index i ON i.dictionary_id = d.rowid > JOIN hanzi h ON h.rowid = i.hanzi_id > WHERE h.traditional = '我' OR h.simplified = '我' > ORDER BY i.relevance desc > > I can get it to

[sqlite] SQL query causes various hangs/crashes/exits

Hi, I hope I'm in the right place - this is my first post to sqlite-users. I'm looking for help on a query I am composing which seems to be blowing-up the query engine/API. Or maybe I'm just missing something very obvious... I have a query which works fine on a small database (15KB) which I

Re: [sqlite] SQL query help

Hmm thanks Roger Table could have a few million rows, i'll have a play and see what the run time is. The relevant column is indexed On 20 August 2011 17:14, Roger Andersson wrote: >  On 08/20/11 05:42 PM, Paul Sanderson wrote: >> Hi all >> >> I am trying to create a query that

Re: [sqlite] SQL query help

On 08/20/11 05:42 PM, Paul Sanderson wrote: > Hi all > > I am trying to create a query that works to craete a subset of a table > based on duplicate items > > Examples work best so consider the contrived table with the following rows > 10 socata > 7 socata > 13 cessna > 2 piper > 7 piper > 55

[sqlite] SQL query help

Hi all I am trying to create a query that works to craete a subset of a table based on duplicate items Examples work best so consider the contrived table with the following rows 10 socata 7 socata 13 cessna 2 piper 7 piper 55 piper 1 diamond I want to see the subset that is 10 socata 7 socata 2

Re: [sqlite] SQL-query execution bug

On Tue, Jan 18, 2011 at 12:07 PM, Vadim Smirnov wrote: > Hello! > I've found a bug in execution queries like this: > SELECT ... FROM table T JOIN table2 T2 ON T2.child=T.master > WHERE T2.attr in(SELECT value FROM table3 T3 JOIN (SELECT group, MAX(value) > FROM table4 T4

[sqlite] SQL-query execution bug

Hello! I've found a bug in execution queries like this: SELECT ... FROM table T JOIN table2 T2 ON T2.child=T.master WHERE T2.attr in(SELECT value FROM table3 T3 JOIN (SELECT group, MAX(value) FROM table4 T4 WHERE T4.date_value<=T2.date_value GROUP BY group) G ON G.group=T3.group) Such queries

Re: [sqlite] SQL query on sort order

If not already done creating a page of additional collations on the wiki would make sense and minimize work all around. > I just mailed you an extension for SQLite offering the collation you need. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] SQL query on sort order

Harish, >We have a problem with a sql query. >In a table, a column called "name" contains character data that may >include >alpha, numeric and special characters. It is required to sort in such >a way >that names starting with alpha characters are listed first, then numerals >and finally

Re: [sqlite] SQL query on sort order

>From the first post I've got the impression that only first character matters for you. When such sort order should persist over all characters you can't do it with simple query. Only the custom collation can help you. Pavel On Wed, Dec 15, 2010 at 7:29 AM, Harish CS wrote:

Re: [sqlite] SQL query on sort order

Harish CS wrote: > Collation is okay for case insensitivity but since here we have digits and > special chars I think it may not be helpful. Plz correct me if I am wrong. You can implement a custom collation that defines any order of your choice. See

Re: [sqlite] SQL query on sort order

Hello Pavel, Thanks. The substr() compares the first character only. For example, if the data is [CAT=$, CAT1$], it has to be sorted as [CAT1$, CAT=$] because when '=' and '1' are compared, '1' has to come first. Thanks for any suggestions. -Harish Pavel Ivanov-2 wrote: > > If you want to

Re: [sqlite] SQL query on sort order

Pavel Ivanov: Thank you very much. We used the query. -Harish Pavel Ivanov-2 wrote: > > If you want to do that completely in SQL without using collations you > can do something like this: > > select name, > case when substr(name, 1, 1) between 'A' and 'Z' or >

Re: [sqlite] SQL query on sort order

Kishor: Collation is okay for case insensitivity but since here we have digits and special chars I think it may not be helpful. Plz correct me if I am wrong. Puneet Kishor-2 wrote: > > > > Harish CS wrote: >> Hi, >> We have a problem with a sql query. >> In a table, a column called "name"

Re: [sqlite] SQL query on sort order

If you want to do that completely in SQL without using collations you can do something like this: select name, case when substr(name, 1, 1) between 'A' and 'Z' or substr(name, 1, 1) between 'a' and 'z' then upper(name) when susbtr(name, 1, 1) between '0' and '9' then '|' ||

Re: [sqlite] SQL query on sort order

Harish CS wrote: > Hi, > We have a problem with a sql query. > In a table, a column called "name" contains character data that may include > alpha, numeric and special characters. It is required to sort in such a way > that names starting with alpha characters are listed first, then numerals >

[sqlite] SQL query on sort order

Hi, We have a problem with a sql query. In a table, a column called "name" contains character data that may include alpha, numeric and special characters. It is required to sort in such a way that names starting with alpha characters are listed first, then numerals and finally special characters.

Re: [sqlite] SQL Query Question

From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: Friday, July 03, 2009 9:19 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] SQL Query Question Rick Ratchford wrote: > For example, say I have 15 Dates already extracted by a p

Re: [sqlite] SQL Query Question

Rick Ratchford wrote: > For example, say I have 15 Dates already extracted by a previous > query. > > I need to now get the 40 records that start at each of those 15 Dates. > > Assuming this is a SORTED dataset in ascending order by Date, I would > need to extract 40 records that start with the

Re: [sqlite] SQL Query Question

hope this example/question is clear. :-^ Cheers! Rick -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John Machin Sent: Friday, July 03, 2009 7:19 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQL Query Ques

Re: [sqlite] SQL Query Question

On 4/07/2009 9:01 AM, Simon Slavin wrote: > On 3 Jul 2009, at 10:03pm, Rick Ratchford wrote: > >> Suppose my 15 Dates are: >> >> 2009-03-03 >> 2008-11-05 >> 2008-07-10 >> ... >> ... >> 2007-07-23 >> >> >> Assuming this is a SORTED dataset in ascending order by Date, I >> would need >> to

Re: [sqlite] SQL Query Question

On 3 Jul 2009, at 10:03pm, Rick Ratchford wrote: > Suppose my 15 Dates are: > > 2009-03-03 > 2008-11-05 > 2008-07-10 > ... > ... > 2007-07-23 > > > Assuming this is a SORTED dataset in ascending order by Date, I > would need > to extract 40 records that start with the record at 2009-03-03,

[sqlite] SQL Query Question

Can someone help me with this? Suppose you have a Table/Recordset that has these columns: Date (string) Color (string) Offset (long) I want to extract from this Table/Recordset 40 contiguous records from 15 locations within the dataset, each referenced by the Date. For example, say I have

Re: [sqlite] sql query with sqlite3_exec

Thanks very much. The link was very useful, and now it is clear to me how basiclly to use sqlite3 C++ API. =) Kees Nuyt wrote: > > On Fri, 12 Jun 2009 07:05:36 -0700 (PDT), sql_newbie > > > http://www.sqlite.org/cvstrac/wiki?p=SimpleCode > -- View this message in context:

Re: [sqlite] sql query with sqlite3_exec

On Fri, 12 Jun 2009 07:05:36 -0700 (PDT), sql_newbie wrote: > >I have another question about sqlite3_exec : > >How can i interact with the database and save the result in a C string for >forther use. For example: > >sqlite3_exec( db, "SELECT FROM urls", NULL, NULL, ); > >How

Re: [sqlite] sql query with sqlite3_exec

I have another question about sqlite3_exec : How can i interact with the database and save the result in a C string for forther use. For example: sqlite3_exec( db, "SELECT FROM urls", NULL, NULL, ); How can i save the returned result-table in a C string for further use in the program?

Re: [sqlite] sql query with sqlite3_exec

Thanks, with help of Friend of mine, we have made the following changes to your statement: rc = sqlite3_exec( db, "DELETE FROM urls where url not in (" + MyURLsArray + ")", NULL, NULL, ); Igor Tandetnik wrote: > > > delete from urls > where url not in ('url1', 'url2', ..., 'url20'); > >

Re: [sqlite] sql query with sqlite3_exec

sql_newbie wrote: > rc = sqlite3_exec( db, "DELETE FROM urls", NULL, NULL, ); > > The previous code will delete everything in the "urls" table and this > is not what i want. > I have a string Array "MyURLsArray" which contains 20 URLs as > strings. My question is: > > How can i format the SQL

[sqlite] sql query with sqlite3_exec

Hi, i am using sqlite3 with C++, and everything is ok. I have a situation and i do not know how to handle it : Let's say i have a database file named "MyDatabase", i am opening this database as follows: sqlite3 *db; int rc; rc = sqlite3_open( "C:\\MyDatabase", ); if ( rc ) {

RE: [sqlite] sql query required

Hi Phanisekhar Try this query.. Select yearofbirth, count(yearofbirth) from group by yearofbirth; -Original Message- From: B V, Phanisekhar [mailto:[EMAIL PROTECTED] Sent: Friday, July 27, 2007 8:31 PM To: sqlite-users@sqlite.org Subject: [sqlite] sql query required Suppose I have

Re: [sqlite] sql query required

Try SELECT Count(Name) FROM tablename GROUP BY yearofbirth On 7/27/07, B V, Phanisekhar <[EMAIL PROTECTED]> wrote: > Suppose I have a table: > > > > Create table "yearofbirth INTEGER, Name string" > > > > What will be the query to identify how many people were born in > different years? The

[sqlite] sql query required

Suppose I have a table: Create table "yearofbirth INTEGER, Name string" What will be the query to identify how many people were born in different years? The output should contain all the years that are present in the table and the total count corresponding to each entry. Eg: 1901

Re: [sqlite] SQL query assistance...

- Original Message - From: "Dan Kennedy" <[EMAIL PROTECTED]> To: <sqlite-users@sqlite.org> Sent: Monday, July 02, 2007 4:17 AM Subject: Re: [sqlite] SQL query assistance... On Sun, 2007-07-01 at 16:31 -0500, Jeff Godfrey wrote: Hi All, Given the following sa

Re: [sqlite] SQL query assistance...

On Sun, 2007-07-01 at 16:31 -0500, Jeff Godfrey wrote: > Hi All, > > Given the following sample data... > > ID Name Version > --- - --- > 1 name1 0.9 > 2 name1 1.0 > 3 name2 1.2 > 4 name3 1.0 > 5 name3 1.7 > 6 name3 1.5 > > I need to create a query that will group

Re: [sqlite] SQL query assistance...

- Original Message - From: "Andrew Finkenstadt" <[EMAIL PROTECTED]> To: <sqlite-users@sqlite.org> Sent: Sunday, July 01, 2007 4:45 PM Subject: Re: [sqlite] SQL query assistance... On 7/1/07, Jeff Godfrey <[EMAIL PROTECTED]> wrote: Hi All, Given the

Re: [sqlite] SQL query assistance...

- Original Message - From: "Gerry Snyder" <[EMAIL PROTECTED]> To: <sqlite-users@sqlite.org> Sent: Sunday, July 01, 2007 4:40 PM Subject: Re: [sqlite] SQL query assistance... Jeff Godfrey wrote: Hi All, Given the following sample data... ID Name Versi

Re: [sqlite] SQL query assistance...

On 7/1/07, Jeff Godfrey <[EMAIL PROTECTED]> wrote: Hi All, Given the following sample data... ID Name Version --- - --- 1 name1 0.9 2 name1 1.0 3 name2 1.2 4 name3 1.0 5 name3 1.7 6 name3 1.5 I need to create a query that will group the data together by Name, but

Re: [sqlite] SQL query assistance...

Jeff Godfrey wrote: Hi All, Given the following sample data... ID Name Version --- - --- 1 name1 0.9 2 name1 1.0 3 name2 1.2 4 name3 1.0 5 name3 1.7 6 name3 1.5 I need to create a query that will group the data together by Name, but for each group will return the

[sqlite] SQL query assistance...

Hi All, Given the following sample data... ID Name Version --- - --- 1 name1 0.9 2 name1 1.0 3 name2 1.2 4 name3 1.0 5 name3 1.7 6 name3 1.5 I need to create a query that will group the data together by Name, but for each group will return the record with the

Re: [sqlite] SQL query help

- Original Message - From: "P Kishor" <[EMAIL PROTECTED]> To: <sqlite-users@sqlite.org> Sent: Monday, June 18, 2007 2:55 PM Subject: Re: [sqlite] SQL query help On 6/18/07, Jeff Godfrey <[EMAIL PROTECTED]> wrote: I have a table which contains (among ot

Re: [sqlite] SQL query help

On 6/18/07, Jeff Godfrey <[EMAIL PROTECTED]> wrote: Not seeing this on the list 1.5 hrs after posting, I thought I'd try again. Sorry if this is a duplicate... Jeff = Hi All, I have a table which contains (among other things), a "name" column and a "version" column (a software asset

[sqlite] SQL query help

Not seeing this on the list 1.5 hrs after posting, I thought I'd try again. Sorry if this is a duplicate... Jeff = Hi All, I have a table which contains (among other things), a "name" column and a "version" column (a software asset table). I need a query that will group all like

[sqlite] SQL query help

Hi All, I have a table which contains (among other things), a "name" column and a "version" column (a software asset table). I need a query that will group all like "names" together in a single record, and return the latest "version" (the largest value) for each group. What I have so far is

Re: [sqlite] SQL query help...

- Original Message - From: "Trey Mack" <[EMAIL PROTECTED]> To: <sqlite-users@sqlite.org> Sent: Friday, June 08, 2007 1:08 PM Subject: Re: [sqlite] SQL query help... Here's what I tried, which didn't work... select name, substr(name,1,length(name)-3) as zone, s

Re: [sqlite] SQL query help...

Here's what I tried, which didn't work... select name, substr(name,1,length(name)-3) as zone, substr(name,length(name)-2,2) as location, max(thick) - min(thick) as diff from plypoint where diff > 0.0005 group by zone,location That causes a "misuse of aggregate" error. select name,

[sqlite] SQL query help...

Hi All, I need a little help in constructing a SQLite query.. Here's what I have so far that works... select name, substr(name,1,length(name)-3) as zone, substr(name,length(name)-2,2) as location, max(thick) - min(thick) as diff from plypoint group by zone,location The above properly

Re: [sqlite] SQL query help (mutiple joins)

Yes, you are right. Good thing the OP found it himself. RBS > actually > > SELECT COUNT(DISTINCT ... > > On 5/1/07, [EMAIL PROTECTED] > <[EMAIL PROTECTED]> wrote: >> It will be as the below query, but replace: >> distinct p.* >> with: >> count(p.ID) >> >> RBS >> >> >> Allan, Mark wrote: >> >> >

Re: [sqlite] SQL query help (mutiple joins)

actually SELECT COUNT(DISTINCT ... On 5/1/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: It will be as the below query, but replace: distinct p.* with: count(p.ID) RBS >> Allan, Mark wrote: >> > What I want is Joe Blogs just the once. >> > >> > >> Mark, >> >> Then try adding distinct like

RE: [sqlite] SQL query help (mutiple joins)

> > Ok, so here's another question, how would I get the count of > patients where the EVC and FVC > 2.0? > Dont worry I have figured this out. I am doing:- select count (distinct p.PatientID) p.* from PatientsTable as p join ExaminationsTable as e on e.PatientID=p.ID join TestTable as t on

RE: [sqlite] SQL query help (mutiple joins)

It will be as the below query, but replace: distinct p.* with: count(p.ID) RBS >> Allan, Mark wrote: >> > What I want is Joe Blogs just the once. >> > >> > >> Mark, >> >> Then try adding distinct like this: >> >> select distinct p.* >> from PatientsTable as p >> join ExaminationsTable as e on

RE: [sqlite] SQL query help (mutiple joins)

> Allan, Mark wrote: > > What I want is Joe Blogs just the once. > > > > > Mark, > > Then try adding distinct like this: > > select distinct p.* > from PatientsTable as p > join ExaminationsTable as e on e.PatientID=p.ID > join TestTable as t on t.ExamID=e.ID > join ForcedSpiroTable as f on

Re: [sqlite] SQL query help (mutiple joins)

Allan, Mark wrote: Excellent, thanks for your help. Mark, For future reference, your posts could use a little more trimming. There is no need to quote the entire string of messages from your original post on each reply. :-) Dennis Cote

Re: [sqlite] SQL query help (mutiple joins)

Allan, Mark wrote: What I want is Joe Blogs just the once. Mark, Then try adding distinct like this: select distinct p.* from PatientsTable as p join ExaminationsTable as e on e.PatientID=p.ID join TestTable as t on t.ExamID=e.ID join ForcedSpiroTable as f on f.TestID=t.ID join

RE: [sqlite] SQL query help (mutiple joins)

g > Subject: Re: [sqlite] SQL query help (mutiple joins) > > > On 5/1/07, Allan, Mark <[EMAIL PROTECTED]> wrote: > > Hi, > > > > Thanks for your quick replies. I have tried this method but > however I am getting a row returned for each entry in > Forc

RE: [sqlite] SQL query help (mutiple joins)

gt; Does this make sense? What I need to do is find all patients that have an > EVC and FVC greater than 2.0. > > Is there a way to do this? Am I missing something? > > Thanks again > > Mark > > >> -Original Message- >> From: Dennis Cote [mailto:[EMAIL PROTE

Re: [sqlite] SQL query help (mutiple joins)

om: Dennis Cote [mailto:[EMAIL PROTECTED] > Sent: 01 May 2007 15:31 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] SQL query help (mutiple joins) > > > Allan, Mark wrote: > > I have a database that looks something like the following:- > > > > PatientsTabl

RE: [sqlite] SQL query help (mutiple joins)

-Original Message- > From: Dennis Cote [mailto:[EMAIL PROTECTED] > Sent: 01 May 2007 15:31 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] SQL query help (mutiple joins) > > > Allan, Mark wrote: > > I have a database that looks something like the following

Re: [sqlite] SQL query help (mutiple joins)

Allan, Mark wrote: I have a database that looks something like the following:- PatientsTable { ID, Name, Sex, } ExaminationsTable { ID, PatientID, } TestTable { ID, ExamID, .} ForcedSpiroTable { ID, TestID, EVC, IVC, IC ... } RelaxedSpiroTable { ID, TestID, FVC, FEV1, PEF, ...} Can

Re: [sqlite] SQL query help (mutiple joins)

select * from PatientsTable P inner join ForcedSpiroTable F on (P.ID = F.ID) inner join RelaxedSpiroTable R on (P.ID = R.ID) where F.EVC > 2.0 and R.FVC > 2.0 RBS > Hi, > > Can anyone offer any help with the following SQL query? > > I have a database that looks something like the following:- >

[sqlite] SQL query help (mutiple joins)

Hi, Can anyone offer any help with the following SQL query? I have a database that looks something like the following:- PatientsTable { ID, Name, Sex, } ExaminationsTable { ID, PatientID, } TestTable { ID, ExamID, .} ForcedSpiroTable { ID, TestID, EVC, IVC, IC ... }

Re: [sqlite] SQL query, finding out which row a result is in

On Mon, Apr 23, 2007 at 09:26:53AM -0400, Stephen Oberholtzer wrote: On 4/22/07, Gilles Roy <[EMAIL PROTECTED]> wrote: Given a arbitrary statement, I need to find out which row a specific result is in, as efficiently as possible. The arbitrary statement can order the results any way it wants.

RE: [sqlite] SQL query, finding out which row a result is in

Hi Roy, If your statement "X" is represented below by "select ... Order by ..." Then would the following give you what you're looking for?? create temp table Xtab as (select Order by ); select ROWID from xTab where MemberID=4567373; (Without some "order by" clause, by the

Re: [sqlite] SQL query, finding out which row a result is in

You don't have to read into a memory array. How about just running through your selection with an sqlite3_step and counting the rows? Gilles Roy wrote: On Sun, Apr 22, 2007 at 05:33:43PM -0500, P Kishor wrote: On 4/22/07, Gilles Roy <[EMAIL PROTECTED]> wrote: Given a arbitrary statement,

Re: [sqlite] SQL query, finding out which row a result is in

On 4/22/07, Gilles Roy <[EMAIL PROTECTED]> wrote: Given a arbitrary statement, I need to find out which row a specific result is in, as efficiently as possible. The arbitrary statement can order the results any way it wants. Let's say your resultset consists of 3 columns: memberid, lastname,

Re: [sqlite] SQL query, finding out which row a result is in

> You could do this: > > SELECT COUNT(*) from X where memberid < 4567373 That assumes that you are sorting by memberid, of course... Hugh - To unsubscribe, send email to [EMAIL PROTECTED]

Re: [sqlite] SQL query, finding out which row a result is in

> That is what I want to do. I want to know where the memberid is in > the list (imagine the list was a waiting list or something). Is there > not a way to just get the row number back? Is seems inefficient to > have to allocate all of the memory to hold all of the results and > then iterate

Re: [sqlite] SQL query, finding out which row a result is in

On 4/22/07, Gilles Roy <[EMAIL PROTECTED]> wrote: On Sun, Apr 22, 2007 at 05:33:43PM -0500, P Kishor wrote: >On 4/22/07, Gilles Roy <[EMAIL PROTECTED]> wrote: >>Given a arbitrary statement, I need to find out which row a specific >>result is in, as efficiently as possible. The arbitrary

Re: [sqlite] SQL query, finding out which row a result is in

On Sun, Apr 22, 2007 at 05:33:43PM -0500, P Kishor wrote: On 4/22/07, Gilles Roy <[EMAIL PROTECTED]> wrote: Given a arbitrary statement, I need to find out which row a specific result is in, as efficiently as possible. The arbitrary statement can order the results any way it wants. what do

Re: [sqlite] SQL query, finding out which row a result is in

Your question is so confusing that I am going to assume there is something you have not been able to express in the asking of it -- On 4/22/07, Gilles Roy <[EMAIL PROTECTED]> wrote: Given a arbitrary statement, I need to find out which row a specific result is in, as efficiently as possible.

  1   2   >