I can't for the life of me figure this out. I'm trying to do a nested select
like this:
SELECT x FROM (( UNION ) INTERSECT ( UNION
)) WHERE X=
Each of the select a through d statements all return the same column x. If I
remove the inner parentheses, it executes just fine but of course the
I have a table of events that have a title, start time, and end time.
The start time is guaranteed unique, so I've made it my primary integer key.
I need all events that overlap the a window of time between say windowstart
and windowend. Currently, the statement
SELECT title FROM event WHERE
This should be simple but apparently it isn't.
I have two tables:
"CREATE TABLE showing ( "
"showingIdINTEGER PRIMARY KEY, "
"stationId INTEGER, "
"startTime INTEGER, "
Wow. Anybody? I figured this would be a simple question for the gurus on this
board. Seriously, nobody knows a better way to do this?
sorka wrote:
>
> This should be simple but apparently it isn't.
>
> I have two tables:
> "
No. This can't be broken down into a query within a query. Perhaps maybe if
you spell out an example of what you're thinking?
Thanks.
sorka wrote:
>
> This should be simple but apparently it isn't.
>
> I have two tables:
> "
It's in my original post above.
sorka wrote:
>
> This should be simple but apparently it isn't.
>
> I have two tables:
> "CREATE TABLE showing ( "
> "showingIdINTEGER PRIMARY KEY, "
>
showing.startTime as startTime,
channel.ChannelMajorNumber as ChannelMajorNumber FROM showing JOIN channel
ON showing.startTime >= 1240362000 AND showing.stationId = channel.stationId
) ORDER BY startTime, ChannelMajorNumber LIMIT 8;
sorka wrote:
>
> This should be simple but apparently it isn'
is super simple and the question is one that anyone
reasonably familiar with sqlite should be able to answer.
sorka wrote:
>
> This should be simple but apparently it isn't.
>
> I have two tables:
> "CREATE TABLE showing ( "
> "
Writing the query as you said you would returns results in nearly instantly,
within a few ms.
The problem is when you add a secondary ordering field. The intended indices
are being used.
The problem, as I've explained several times already is that there is no way
to create a multicolumn index
WHERE showing.startTime >= 123923000
> AND showing.stationId IN ( SELECT DISTINCT channel.stationId FROM
> channel )
>LIMIT 8;
>
> enjoy,
>
> -jeremy
>
>
> On Tue, Apr 21, 2009 at 08:58:56PM -0700, sorka wrote:
>>
>> Writing the query as
ion about the whole problem is going to be in
> necessary to
> help further. For instance, it appears you have a table of 'showings' and
> a
> table of 'channels' and the goal is, find the next N items from the
> showings
> table for channels in the channel table.
>
> enj
Hi. I have a table that stores pieces of an image as a bunch of blobs. I get
the pieces out of order and store them in a table until I get all of the
pieces I need. I then want to assemble them in order and store the resulting
complete image in in another table entirely.
Is there a smart way to
Is there any way to have an intsert into an FTS3 table ignore a row if the
ROWID being inserted already exists? This is turning out to be quite
troublesome because I'm inserting thousands of records where just a few like
3 or 4 will have the same rowid as existing records. However, to do the test
I'm doing both delete and insert within the same transaction already. The
problem is there will alway be a few duplicates out of the hundreds of
records so it will always fail. For whatever reason, the delete, even though
it's just 2 or 3 records is taking 10 times longer than just the insert
no duplicate checking at all when the explicit key name
is used but then it's useless as I can't have duplicate keys.
Simon Slavin-3 wrote:
>
>
> On 4 Nov 2009, at 5:12am, sorka wrote:
>
>> Is there any way to have an intsert into an FTS3 table ignore a row
>> if the
Slavin-3 wrote:
>
>
> On 4 Nov 2009, at 5:05pm, sorka wrote:
>
>> Hmm. Have you actually tried this yourself?
>>
>> Here's what I get with a simplified example:
>>
>> CREATE VIRTUAL TABLE keyword using FTS3(programId INTEGER PRIMARY KEY,
>> tit
This is driving me nuts. I have two tables I'm trying to join together on two
text fields.
CREATE TABLE tmp_role ( programId INTEGER,
roleNameINTEGER,
positionINTEGER,
isNew BOOL,
personIdINTEGER,
nameSTRING);
This table has up to a few dozen records at
Thank you! That was it. I've been pulling my hair out over this all day.
I should have seen it. I've never used STRING in my own tables and I
inherited this from someone else and didn't even think twice that the type
difference would be the issue.
Thanks you again.
sorka wrote
I'm sitting here banging my head trying to decide the subject for this post
because I don't know what I'd call what I want to do :)
Here's what I want to do but don't know how. The schema is simplified for
discussion.
CREATE TABLE program (time_received INTEGER, name TEXT);
Assume indices where
OK, it just hit me that I can use a subselect to get the 50th recorder
ordered by time and use a LIMIT 1 OFFSET 50 to get the time at that
location.
I should be be able to use that a MAX result.I hope.:)
--
View this message in context:
We're seeing exactly the same thing on .22. We were previously on .17 and
never had this issue.
Our usage is exactly as you describe as well. Multiple threads with the
shared cache enabled but no single thread is using the same connection more
than once.
--
View this message in context:
If I run the statement:
delete from keyword where rowid in (SELECT idToDelete FROM
keywordDeleteList);
This statement takes an eternity even if there are only say 5 records in
keywordDeleteList.
Same thing if I do this:
delete from keyword where rowid in (418458, 418541, 421168, 421326,
22 matches
Mail list logo
