Dennis Cote came up with this, which I think will work...
select t.* from t join (select max(a) as a, b from t group by b) as key
where t.a=key.a and t.b=key.b;
Thanks for all the suggestions.
Tim
DRH> This is about the bazillionth request for the older 2.8 behavior
DRH> that provided less concurrency. I'll try to have a low-concurrency
DRH> solution installed in 3.0 by the end of the week.
Well, I wasn't actually requesting a code change, just a suggestion for
a work-around the change in
On Mon, 4 Oct 2004, D. Richard Hipp wrote:
but still, there are no blocking locks correct? and, by extension, beginning
a transaction could still result in busy being returned correct?
regards.
-a
--
===
| EMAIL :: Ara
Well, isn't the point of the HAVING clause?
SELECT MAX(A), B FROM T
GROUP BY B
HAVING B=5
Fred
The code in CVS contains enhancements to transactions to help
people work around the so-called "deadlock" issues they have been
having. Those who are able are encouraged to try out the latest
code in CVS. If no problems are reported within the next week,
I will release version 3.0.8 that
[EMAIL PROTECTED] wrote:
Question, why as the encode and decode funtions been removed?
Did they perform badly, are there bugs? Caveats?
SQLite version 2.8 and earlier could not (easily) store binary
data - data with embedded \000 characters. Thus the encode/decode
routines were provide to
Thanks... that did it.
D. Richard Hipp wrote:
Eric Scouten wrote:
I'm struggling with implementing a notification system using triggers
and custom functions. Here's what I'd like to accomplish: SQLite
notifies me for every time a row is inserted, updated, or deleted in
a given table. Here's how
If you use BLOBs I do not think you need sqlite_encode_binary and
sqlite_decode_binary
On Tue, 2004-10-05 at 02:30, [EMAIL PROTECTED] wrote:
> Hello,
>
> I am new to sqlite. So far I've been very impressed by it. We
> are using it as the backend of an open-source MAPI message store.
> Still
Eric Scouten wrote:
I'm struggling with implementing a notification system using triggers
and custom functions. Here's what I'd like to accomplish: SQLite
notifies me for every time a row is inserted, updated, or deleted in a
given table. Here's how I've attempted it so far (so far for
Hello,
I am new to sqlite. So far I've been very impressed by it. We
are using it as the backend of an open-source MAPI message store.
Still struggling with locking issues, but looking alright.
Question, why as the encode and decode funtions been removed?
Did they perform badly, are there
Replying to my own post, sorry :-)
Being pedantic here, when I said the "correct" way of doing what is
required was...
> select * from t
> where (a, b) in (select max(a), b from t group by b);
that would work for ORACLE, but is not ANSI as such.
The ANSI method would be...
select * from t x
I'm struggling with implementing a notification system using triggers
and custom functions. Here's what I'd like to accomplish: SQLite
notifies me for every time a row is inserted, updated, or deleted in a
given table. Here's how I've attempted it so far (so far for insertions
only):
I added
> SELECT MAX(A) AS A, B, C
> FROM T
> GROUP BY B
>
This is an invalid SQL statement (SQLite should generate an error here).
The correct(tm) way to do this is with subqueries.
ORACLE SQL (and others), using subqueries, you would use...
select * from t
where (a, b) in (select max(a), b from t
> The statement proposed by Lawrence (copied below) will work
> if your table has only these two columns, A and B.
>
> SELECT MAX(A) AS A , B
> FROM T
> GROUP BY B
>
> From your question I got the imprssion you may have other
> columns as well.
>
> For a table t like this;
>
> a|b|c
> 1|5|6
>
> > Given a table T like this:
>
> > A B
> > -
> > 1 5
> > 2 5
> > 3 5
> >
> > I need a query to give me just the row with the largest A
> value, within
> > a group defined by B. In this case, it would be the row with A = 3.
>
> SELECT MAX(A) AS A , B
> FROM T
>
Tim McDaniel wrote:
> Given a table T like this:
>
> A B
> -
> 1 5
> 2 5
> 3 5
>
> I need a query to give me just the row with the largest A value,
> within a group defined by B.
Tim,
The statement proposed by Lawrence (copied below) will work if your table
has only
Dennis Cote wrote:
>
> I have attached that comment to this message.
>
Oops... my attachement didn't make it through. (Are they blocked?)
Here is the relevent comment:
/*
** 2004 April 6
**
** The author disclaims copyright to this source code. In place of
** a legal notice, here is a
I'm not sure if you can do it in one query, but you can definitely do it
in two:
SELECT max(A) FROM T WHERE B = 5;
SELECT * FROM T WHERE A = ? AND B = 5; -- bind ? to max(a) above
Tim McDaniel wrote:
All,
This should be easy, but this non SQL guru can't figure it out...
Given a table T like
- Original Message -
From: "Tim McDaniel" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, October 04, 2004 11:23 PM
Subject: [sqlite] SQL help
> Given a table T like this:
> A B
> -
> 1 5
> 2 5
> 3 5
>
> I need a query to give me just the row with
Chris Bruyere wrote:
> 1) How is the database stored?
>
> I've been looking on the webpages and search the ngs
> but can't find anything like this. About a month ago I
> remember finding a doc which discusses how the data is
> stored but now I can't find it. Did it disappear? Or
> am I just wrong?
All,
This should be easy, but this non SQL guru can't figure it out...
Given a table T like this:
A B
-
1 5
2 5
3 5
I need a query to give me just the row with the largest A value, within
a group defined by B. In this case, it would be the row with A = 3.
Something
Chris
There was a seperate download for WINCE, and Windows sqlite version
2.8.15. For the new current version 3.0.7 I can not find seperate
downloads. If you do find a WINCE version of sqlite for 3.0.7 would you
let me know.
Thanks
Steve Frierdich
Chris Bruyere wrote:
Hi All! We're looking
Hi All! We're looking at using SQLite as an embedded
database. I have a couple of questions:
1) How is the database stored?
2) How could we share database files. We would like to
be able to export the database files and then import
them into another database.
Any ideas?
I've been looking on
> Brass Tilde wrote:
> > I was hoping for some database wide setting (using 3.x). Is there not
one?
>
> The default collating sequences is called BINARY. You can redefine
> BINARY to be the same as NOCASE if you want. Then when tables use
> the default collating sequence, they will use NOCASE
Brass Tilde wrote:
I was hoping for some database wide setting (using 3.x). Is there not one?
The default collating sequences is called BINARY. You can redefine
BINARY to be the same as NOCASE if you want. Then when tables use
the default collating sequence, they will use NOCASE instead of
> definitions. There is already a built-in NOCASE collation that works for
> US-Latin characters. You'll need to do something more advanced for
I am using US-Latin characters.
> international characters sets, however. If you need i18n support,
> you can still use the built-in NOCASE collation
Brass Tilde wrote:
Is there a setting that I can use when opening a database, or when creating
it, or whatever, so that queries against textual fields in the tables are
*not* case sensitive?
You can define a new collating sequence using the sqlite3_create_collation()
API then use that collating
Is there a setting that I can use when opening a database, or when creating
it, or whatever, so that queries against textual fields in the tables are
*not* case sensitive? Or is the fact that SQLite is typeless going to get
in the way of this?
For instance, I want to be able to do:
select
Taka Muraoka wrote:
Under 2.8.11, thread 2 would block at (2) until thread 1 completed its
update. Under 3.0.7, thread 2 continues on to but gets a SQLITE_BUSY
error. Furthermore, there is no way to handle it other than to roll
everything back and try again. In other words, every single place
I guess what I'm really asking is - are there any circumstances under which
SQLite 2.8.14 writes to common (shared among all threads) data structures,
either in memory or on disk, during a call to sqlite_close() ?
Not that I know of. But I might have missed something. Why don't you
check the
Kurt Welgehausen wrote:
Perhaps,
http://www.mail-archive.com/[EMAIL PROTECTED]/msg02334.html
Regards
Thanks for the response, however it does not quite fit in with what I saw.
I guess what I'm really asking is - are there any circumstances under which
SQLite 2.8.14 writes to common (shared
To answer my own question...
TM> We're upgrading an app from 2.8.11 to 3.0.7 and are seeing fairly
TM> frequent "database is locked" errors that would be explained if this
TM> were not the case. It seems to be happening when two threads start a
TM> transaction; one does its first INSERT ok but
32 matches
Mail list logo
