Re: [sqlite] Last record

[David Raymond]

"Keith, what if one has a peanut allergy?"

Well, the maid dutifully logs the changes she makes to the tin, so that in the 
event of an anaphylactic crash the tin can be returned to its original state. 
This helps ensure we have ACID peanuts.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record

[Wolfgang Enzinger]

Am Tue, 15 Oct 2019 13:36:37 -0800 schrieb Adam Levy:

> Although what Doug suggested could work, it doesn't make sense to me to add
> an extra count or max query just to know how many results will be returned
> when those results will be queried anyway.

One typical use case I can think of is that one wants to display a progress
bar during population of a list control with a query result. Impossible as
long as you don't know beforehand what 100% is by means of an absolute
number.

Maybe that's the reason why those percentage based progress bars are more
and more replaced by animation controls nowadays that just indicate that
something is still going on, without any kind of prediction how much more
time it will take. ;-)

Wolfgang

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record

[Warren Young]

On Oct 15, 2019, at 1:52 PM, Don V Nielsen  wrote:
> 
> what if one has a peanut allergy?

You’re joking, but it gives us cause to extend the fable profitably: use the 
proper WHERE clause.

SELECT * FROM food WHERE type != 'peanuts'

The maid delivers whatever you ask for, within the limits specified within the 
fable.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record

[Adam Levy]

Why can't the knowledge of the "last row" be obtained and used by the
application after _step returns SQLITE_DONE?

Although what Doug suggested could work, it doesn't make sense to me to add
an extra count or max query just to know how many results will be returned
when those results will be queried anyway.


On Tue, Oct 15, 2019, 1:16 PM Doug  wrote:

> How about something like this that costs more to run:
>
> Given a table T with columns A, B, C,..
> BEGIN TRANSACTION
> SELECT Count(*) AS Count [filter spec];
> SELECT A,B,C,... [filter spec];
> ROLLBACK or COMMIT
>
> Doug
>
> > -Original Message-
> > From: sqlite-users 
> > On Behalf Of Simon Slavin
> > Sent: Tuesday, October 15, 2019 8:35 AM
> > To: SQLite mailing list 
> > Subject: Re: [sqlite] Last record
> >
> > On 15 Oct 2019, at 4:34pm, Philippe RIO <51...@protonmail.ch>
> > wrote:
> >
> > > how could I know if I am reading the last record with
> > > sqlite  (sqlite3_step)?
> >
> > Sorry, there's no way to do that for some arbitrary SELECT.
> > Because SQLite itself may not know.
> >
> > SQLite does not always process your query and store all the
> > results in memory.  If there's an ideal index for your query, each
> > call to _step() just one more row.  SQLite itself doesn't know it
> > has reached the end until it gets an error because it runs off the
> > end of the index.
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> > users
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record

[Doug]

How about something like this that costs more to run:

Given a table T with columns A, B, C,..
BEGIN TRANSACTION
SELECT Count(*) AS Count [filter spec];
SELECT A,B,C,... [filter spec];
ROLLBACK or COMMIT

Doug

> -Original Message-
> From: sqlite-users 
> On Behalf Of Simon Slavin
> Sent: Tuesday, October 15, 2019 8:35 AM
> To: SQLite mailing list 
> Subject: Re: [sqlite] Last record
> 
> On 15 Oct 2019, at 4:34pm, Philippe RIO <51...@protonmail.ch>
> wrote:
> 
> > how could I know if I am reading the last record with
> > sqlite  (sqlite3_step)?
> 
> Sorry, there's no way to do that for some arbitrary SELECT.
> Because SQLite itself may not know.
> 
> SQLite does not always process your query and store all the
> results in memory.  If there's an ideal index for your query, each
> call to _step() just one more row.  SQLite itself doesn't know it
> has reached the end until it gets an error because it runs off the
> end of the index.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record

[Keith Medcalf]

On Tuesday, 15 October, 2019 13:52, Don V Nielsen  wrote:

>Keith, what if one has a peanut allergy?

Well, if one were allergic to beans (colloquially called peanuts for some 
reason, even though they are not nuts) then I suppose real nuts would do.

I have a big tin of mixed Honey Roasted Nuts (Cashews, Almonds and Pistachios, 
so says the label) right beside me.  Though it does say that besides those and 
other nuts it might also contain beans (peanuts) and bean (peanut) oil ... so 
obviously YMMV :)

Interestingly I also have nearby an empty generic (Presidents Choice) plastic 
bottle of Honey Roasted Peanuts (the maid is not so efficient here at throwing 
the empties in the refuse).  The label, interestingly enough, warns that it 
might also contain actual nuts.

Perhaps I should have used Gummy Bears.  Are people allergic to those?  Do they 
even come in a tin (so you cannot see what is inside)?  
Or perhaps Sardines.  For sure those come in tins and do not contain beans 
(peanuts) or even real nuts ... or at least the Brunswick ones don't.

:)

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record

[Don V Nielsen]

Keith, what if one has a peanut allergy?

On Tue, Oct 15, 2019 at 1:33 PM Jose Isaias Cabrera 
wrote:

>
>
> Keith Medcalf, on Tuesday, October 15, 2019 02:26 PM, wrote...
> >
> >
> > On Tuesday, 15 October, 2019 09:35, Philippe RIO, on
> >
> > >A short question : how could I know if I am reading the last record with
> > >sqlite  (sqlite3_step)? sqlite3_step only returns SQLITE_ROW. Is there a
> > >function for that case which returns SQLITE_DONE? A function which is
> one
> > >record in advance from sqlite3_step.
> >
> > When sqlite3_step returns SQLITE_DONE there are no more rows.
> >
> > A prepared statement is a row generator.  Each time you ask it to
> produce a
> > row (sqlite3_step) it does whatever needs to be done to get you the next
> row
> > and gives it to you (SQLITE_ROW).  Eventually it cannot generate another
> row
> > and you get the message that the generator is empty (SQLITE_DONE).
>
> []clip]
>
> > The only way that you have to know that the tin is empty is that when
> you ask
> > for a peanut the maid replies "Sorry luv, but the tin is empty", rather
> than
> > giving you a peanut.
>
> Keith, may I say thank you.  Your parables are so teach-friendly, and more
> than that, funny.  I appreciate them. Thanks.
>
> josé
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record

[Jose Isaias Cabrera]

Keith Medcalf, on Tuesday, October 15, 2019 02:26 PM, wrote...
>
>
> On Tuesday, 15 October, 2019 09:35, Philippe RIO, on
>
> >A short question : how could I know if I am reading the last record with
> >sqlite  (sqlite3_step)? sqlite3_step only returns SQLITE_ROW. Is there a
> >function for that case which returns SQLITE_DONE? A function which is one
> >record in advance from sqlite3_step.
>
> When sqlite3_step returns SQLITE_DONE there are no more rows.
>
> A prepared statement is a row generator.  Each time you ask it to produce a
> row (sqlite3_step) it does whatever needs to be done to get you the next row
> and gives it to you (SQLITE_ROW).  Eventually it cannot generate another row
> and you get the message that the generator is empty (SQLITE_DONE).

[]clip]

> The only way that you have to know that the tin is empty is that when you ask
> for a peanut the maid replies "Sorry luv, but the tin is empty", rather than
> giving you a peanut.

Keith, may I say thank you.  Your parables are so teach-friendly, and more than 
that, funny.  I appreciate them. Thanks.

josé
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record

[Keith Medcalf]

On Tuesday, 15 October, 2019 09:35, Philippe RIO <51...@protonmail.ch> wrote:

>A short question : how could I know if I am reading the last record with
>sqlite  (sqlite3_step)? sqlite3_step only returns SQLITE_ROW. Is there a
>function for that case which returns SQLITE_DONE? A function which is one
>record in advance from sqlite3_step.

When sqlite3_step returns SQLITE_DONE there are no more rows.

A prepared statement is a row generator.  Each time you ask it to produce a row 
(sqlite3_step) it does whatever needs to be done to get you the next row and 
gives it to you (SQLITE_ROW).  Eventually it cannot generate another row and 
you get the message that the generator is empty (SQLITE_DONE).

Think of sqlite3 as a maid.  You place your order for peanuts with the maid 
(sqlite3_prepare).  The maid then tells you whether the order was accepted or 
not (SQLITE_OK or some other return code).  Every time you want a peanut you 
tell the maid to give you a peanut (sqlite3_exec).  If this is the first time 
that you have asked for a peanut, then the maid will have to run to the store 
and buy a tin of peanuts, open the tin, and give you a peanut (SQLITE_ROW) -- 
though it might be discovered that the tin is empty to begin with, in which 
case the maid will tell you so when you first ask for a peanut (SQLITE_DONE).  
Each time subsequently that you ask for a peanut the maid will simply give you 
another peanut from the tin.  Eventually, the tin will be empty and the maid 
will toss the empty tin in the rubbish and tell you that the tin is empty 
(SQLITE_DONE).  You may ask for another peanut in which case the maid will 
start all over again and go to the store for another tin of peanuts.  Lather, 
rinse, repeat.  Sometimes the store may be out of tins of peanuts, or the maid 
may get hit by a bus and killed on the way to and from the store, or may die of 
a heart attack in between you requesting peanuts.  In this case the butler will 
give you an error indication (SQLITE_ERROR) telling you what went awry.

Your only way of getting peanuts is to ask the maid for one, you cannot see the 
tin nor can you look inside it.  The only way that you have to know that the 
tin is empty is that when you ask for a peanut the maid replies "Sorry luv, but 
the tin is empty", rather than giving you a peanut.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record

[Chris Green]

Philippe RIO <51...@protonmail.ch> wrote:
> A short question : how could I know if I am reading the last record with
> sqlite  (sqlite3_step)? sqlite3_step only returns SQLITE_ROW. Is there a
> function for that case which returns SQLITE_DONE? A function which is one
> record in advance from sqlite3_step.
> 
What do you mean by "last record"?

-- 
Chris Green
·

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record

[Don V Nielsen]

Pardon me for being thick. But the end of what? The end of the sqlite file?
The end of a table? The end of a select?

I always thought there was no such thing as "a start or an end" as the
database is basically air until you request something from it. Even when
you have something, it could change in the very next exec of the query. So
what is the "start" and what is the "end"?

Looking at a sqlite file as a whole, is it the row at the very end of the
file or the last row inserted, which could be located in some page that is
not even the end of the file.

I don't think I understand too well.

On Tue, Oct 15, 2019 at 11:53 AM Jens Alfke  wrote:

>
>
> > On Oct 15, 2019, at 9:44 AM, Simon Slavin  wrote:
> >
> > You can then remember the value(s) of some column(s) of the row
> returned, and watch for the same one(s) when you do your desired SELECT.
>
> As long as all the result rows are unique…
>
> All of these workaround seem more expensive/complex than just adapting
> your code so it doesn't have to know the last row in advance.
>
> —Jens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record

[Jens Alfke]

> On Oct 15, 2019, at 9:44 AM, Simon Slavin  wrote:
> 
> You can then remember the value(s) of some column(s) of the row returned, and 
> watch for the same one(s) when you do your desired SELECT.

As long as all the result rows are unique…

All of these workaround seem more expensive/complex than just adapting your 
code so it doesn't have to know the last row in advance.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record

[Jim Morris]

In your application you can create a wrapping iterator that pre-reads
the next value instead or directly accessing the low level step
function.  Then you can ask if it is the last.

On 10/15/2019 9:44 AM, Simon Slavin wrote:
> On 15 Oct 2019, at 5:38pm, Philippe RIO <51...@protonmail.ch> wrote:
>
>> The only way is to make a query for getting the number of records and in the 
>> second query I have to count the number of records retrieved to know if it 
>> is the last one.
> Assume your intended query specifies an order which has unique keys (in other 
> words, you know exactly what order rows will be answered in.  Suppose it is
>
> SELECT ... ORDER BY a, b DESC, c
>
> You can find the last row which will be returned by reversing the order and 
> adding LIMIT 1
>
> SELECT ... ORDER BY a DESC, b ASC, c DESC LIMIT 1
>
> You can then remember the value(s) of some column(s) of the row returned, and 
> watch for the same one(s) when you do your desired SELECT.
>
> Depending on the number of rows in your result set, this may or may not be 
> faster than counting the number of rows.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record

[Simon Slavin]

On 15 Oct 2019, at 5:38pm, Philippe RIO <51...@protonmail.ch> wrote:

> The only way is to make a query for getting the number of records and in the 
> second query I have to count the number of records retrieved to know if it is 
> the last one.

Assume your intended query specifies an order which has unique keys (in other 
words, you know exactly what order rows will be answered in.  Suppose it is

SELECT ... ORDER BY a, b DESC, c

You can find the last row which will be returned by reversing the order and 
adding LIMIT 1

SELECT ... ORDER BY a DESC, b ASC, c DESC LIMIT 1

You can then remember the value(s) of some column(s) of the row returned, and 
watch for the same one(s) when you do your desired SELECT.

Depending on the number of rows in your result set, this may or may not be 
faster than counting the number of rows.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record

[Philippe RIO]

The only way is to make a query for getting the number of records and in the
second query I have to count the number of records retrieved to know if it
is the last one.

Thank you for the quick answer.



-

Kenavo

https://md5finder.blogspot.com/
(Never be pleased, always improve)
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record

[Simon Slavin]

On 15 Oct 2019, at 4:34pm, Philippe RIO <51...@protonmail.ch> wrote:

> how could I know if I am reading the last record with
> sqlite  (sqlite3_step)?

Sorry, there's no way to do that for some arbitrary SELECT.  Because SQLite 
itself may not know.

SQLite does not always process your query and store all the results in memory.  
If there's an ideal index for your query, each call to _step() just one more 
row.  SQLite itself doesn't know it has reached the end until it gets an error 
because it runs off the end of the index.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Last record

[Philippe RIO]

A short question : how could I know if I am reading the last record with
sqlite  (sqlite3_step)? sqlite3_step only returns SQLITE_ROW. Is there a
function for that case which returns SQLITE_DONE? A function which is one
record in advance from sqlite3_step.

Thank every one



-

Kenavo

https://md5finder.blogspot.com/
(Never be pleased, always improve)
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record in db

[Kit]

2011/8/22 Black, Michael (IS) :
> sqlite> create table t1(id integer primary key autoincrement,data text);
> :
> sqlite> select rowid,* from t1;
> :
> 5|5|two    << This should be 4,5 and not 5,5 according to the docs as "3" was 
> the largest in the table prior to insert.

Column `id` is an alias `rowid`, 3rd paragraph:
http://www.sqlite.org/lang_createtable.html#rowid
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record in db

[Black, Michael (IS)]

last_insert_row_id isn't guaranteed either...and here's the complete example of 
using autoincrement that is guaranteed to work and not be volatile.

D:\SQLite>sqlite3 t1.db
SQLite version 3.7.4
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t1 (id integer primary key autoincrement,data text);
sqlite> insert into t1 values(null,'one');
sqlite> insert into t1 values(null,'two');
sqlite> insert into t1 values(null,'three');
sqlite> .quit
D:\SQLite>sqlite3 t1.db
SQLite version 3.7.4
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select last_insert_rowid() from t1 limit 1;
0

last_insert_rowid is volatile.

sqlite> select id,data from t1 order by id desc limit 1;
3,three
sqlite> delete from t1 where data='two';
sqlite> insert into t1 values(null,'two');
sqlite> select id,data from t1 order by id desc limit 1;
4|two
sqlite>.quit
D:\SQLite>sqlite3 t1.db
SQLite version 3.7.4
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select id,data from t1 order by id desc limit 1;
4|two



Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Igor Tandetnik [itandet...@mvps.org]
Sent: Monday, August 22, 2011 3:01 PM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] Last record in db


On 8/22/2011 3:43 PM, Black, Michael (IS) wrote:
> I thought we were answering the question "how can I retrive the last row" -- 
> though we never got a definition of what "last" meant.  I assumed "last 
> inserted".

Defining the meaning of the word "last" in terms of an expression that
includes the word "last" is a bit circular. Let's say, "most recently
inserted".

> Sure you can construct an example that doesn't work.  But he didn't ask how 
> NOT to do it.

OK then, show how to do it. You haven't, yet.

> Don't you agree that using autoincrement properly guarantees
> retrieving the last inserted row? Or are you maintaining that is a false 
> statement?

I maintain that the request you have shown - select id, data from Auto
where id=(select max(id) from Auto); - doesn't always retrieve the most
recently inserted row, and thus doesn't in fact solve the problem you
claim it solves.

>  I'd like to see an example to disprove it if you maintain that its false.

I have shown one.
--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record in db

[Black, Michael (IS)]

According to the docs rowid is not guaranteed to be monotonic.

So this is not guaranteed to give the right answer.

SELECT ... ORDER BY rowid DESC LIMIT 1

However, define your own autoincrement (myid) and it is.

SELECT ... ORDER BY myid DESC LIMIT 1




Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Simon Slavin [slav...@bigfraud.org]
Sent: Monday, August 22, 2011 2:49 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Last record in db


On 22 Aug 2011, at 8:43pm, Black, Michael (IS) wrote:

> I thought we were answering the question "how can I retrive the last row" -- 
> though we never got a definition of what "last" meant. [snip]

which is, of course, the problem with that question.

> Don't you agree that using autoincrement properly guarantees retrieving the 
> last inserted row?

As long as you're not messing about with the way SQLite does things, using the

SELECT ... ORDER BY rowid DESC LIMIT 1

form is as good an answer as any.

By the way, I don't think anyone has mentioned either

SELECT last_insert_rowid() FROM myTable

or the

sqlite3_int64 sqlite3_last_insert_rowid(sqlite3*)

C function yet.

Simon.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record in db

[Simon Slavin]

On 22 Aug 2011, at 8:43pm, Black, Michael (IS) wrote:

> I thought we were answering the question "how can I retrive the last row" -- 
> though we never got a definition of what "last" meant. [snip]

which is, of course, the problem with that question.

> Don't you agree that using autoincrement properly guarantees retrieving the 
> last inserted row?

As long as you're not messing about with the way SQLite does things, using the

SELECT ... ORDER BY rowid DESC LIMIT 1

form is as good an answer as any.

By the way, I don't think anyone has mentioned either

SELECT last_insert_rowid() FROM myTable

or the

sqlite3_int64 sqlite3_last_insert_rowid(sqlite3*)

C function yet.

Simon.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record in db

[Black, Michael (IS)]

I thought we were answering the question "how can I retrive the last row" -- 
though we never got a definition of what "last" meant.  I assumed "last 
inserted".



Sure you can construct an example that doesn't work.  But he didn't ask how NOT 
to do it.



Don't you agree that using autoincrement properly guarantees retrieving the 
last inserted row?  Or are you maintaining that is a false statement?  I'd like 
to see an example to disprove it if you maintain that its false.



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Igor Tandetnik [itandet...@mvps.org]
Sent: Monday, August 22, 2011 1:14 PM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] Last record in db

On 8/22/2011 1:56 PM, Black, Michael (IS) wrote:
> Ahhh...you didn't let autoincrement do it's job...

Yes, quite intentionally, in order to emphasize the point that "record
with the largest rowid" and "record inserted most recently" are not
necessarily one and the same, whether or not AUTOINCREMENT was specified
when the table was created.

Of course it's possible to construct an example where the same record is
both most recently inserted and has the largest rowid. But it's also
possible to construct an example where these are two different records.
--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record in db

[Igor Tandetnik]

On 8/22/2011 1:56 PM, Black, Michael (IS) wrote:
> Ahhh...you didn't let autoincrement do it's job...

Yes, quite intentionally, in order to emphasize the point that "record 
with the largest rowid" and "record inserted most recently" are not 
necessarily one and the same, whether or not AUTOINCREMENT was specified 
when the table was created.

Of course it's possible to construct an example where the same record is 
both most recently inserted and has the largest rowid. But it's also 
possible to construct an example where these are two different records.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record in db

[Black, Michael (IS)]

Ahhh...you didn't let autoincrement do it's job...





sqlite> create table Auto(id integer primary key autoincrement, data text);
sqlite> insert into Auto values (NULL, 'one');
sqlite> insert into Auto values (NULL, 'two');
sqlite> insert into Auto values (NULL, 'three');
sqlite> select rowid, data from Auto where rowid=(select max(rowid) from Auto);
3|three
sqlite> delete from Auto where data='two';
sqlite> insert into Auto values(NULL, 'most recent');
sqlite> select id, data from Auto where id=(select max(id) from Auto);
4|most recent



I wouldn't trust rowid given the description that it can reuse numbers.





Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Igor Tandetnik [itandet...@mvps.org]
Sent: Monday, August 22, 2011 12:44 PM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] Last record in db

On 8/22/2011 1:33 PM, Black, Michael (IS) wrote:
> That's because my id is autoincrement and yours is not.

What do you mean, mine vs yours? I continue with your example, using the
same setup.

To avoid any confusion, here's a full session:

sqlite> create table NoAuto(id integer primary key, data text);
sqlite> insert into NoAuto values (NULL, 'one');
sqlite> insert into NoAuto values (NULL, 'two');
sqlite> insert into NoAuto values (NULL, 'three');
sqlite> select rowid, data from NoAuto where rowid=(select max(rowid)
from NoAuto);
3|three
sqlite> delete from NoAuto where data='two';
sqlite> insert into NoAuto values(2, 'most recent');
sqlite> select rowid, data from NoAuto where rowid=(select max(rowid)
from NoAuto);
3|three



sqlite> create table Auto(id integer primary key autoincrement, data text);
sqlite> insert into Auto values (NULL, 'one');
sqlite> insert into Auto values (NULL, 'two');
sqlite> insert into Auto values (NULL, 'three');
sqlite> select rowid, data from Auto where rowid=(select max(rowid) from
Auto);
3|three
sqlite> delete from Auto where data='two';
sqlite> insert into Auto values(2, 'most recent');
sqlite> select rowid, data from Auto where rowid=(select max(rowid) from
Auto);
3|three



Note how the record with data=='most recent' was never selected, despite
being inserted by the most recent successful INSERT statement.
--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record in db

[Igor Tandetnik]

On 8/22/2011 1:33 PM, Black, Michael (IS) wrote:
> That's because my id is autoincrement and yours is not.

What do you mean, mine vs yours? I continue with your example, using the 
same setup.

To avoid any confusion, here's a full session:

sqlite> create table NoAuto(id integer primary key, data text);
sqlite> insert into NoAuto values (NULL, 'one');
sqlite> insert into NoAuto values (NULL, 'two');
sqlite> insert into NoAuto values (NULL, 'three');
sqlite> select rowid, data from NoAuto where rowid=(select max(rowid) 
from NoAuto);
3|three
sqlite> delete from NoAuto where data='two';
sqlite> insert into NoAuto values(2, 'most recent');
sqlite> select rowid, data from NoAuto where rowid=(select max(rowid) 
from NoAuto);
3|three



sqlite> create table Auto(id integer primary key autoincrement, data text);
sqlite> insert into Auto values (NULL, 'one');
sqlite> insert into Auto values (NULL, 'two');
sqlite> insert into Auto values (NULL, 'three');
sqlite> select rowid, data from Auto where rowid=(select max(rowid) from 
Auto);
3|three
sqlite> delete from Auto where data='two';
sqlite> insert into Auto values(2, 'most recent');
sqlite> select rowid, data from Auto where rowid=(select max(rowid) from 
Auto);
3|three



Note how the record with data=='most recent' was never selected, despite 
being inserted by the most recent successful INSERT statement.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record in db

[Black, Michael (IS)]

That's because my id is autoincrement and yours is not.





Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Igor Tandetnik [itandet...@mvps.org]
Sent: Monday, August 22, 2011 12:18 PM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] Last record in db

On 8/22/2011 1:11 PM, Black, Michael (IS) wrote:
> Sure it does.
>
> sqlite>  select rowid,id,* from t1 where id=(select max(id) from t1);
> 4|4|4|two
>
>
>
> "two" was the last succesful insert in my example.

But 'another two' (id==2) was the last successful insert in my extension
of your example. Yet your statement still returns 4.


> I'm now noticing though that rowid is not working as documented.
>
> http://www.sqlite.org/autoinc.html
>
> Says "If no ROWID is specified on the insert, or if the specified
> ROWID has a value of NULL, then an appropriate ROWID is created
> automatically. The usual algorithm is to give the newly created row a
> ROWID that is one larger than the largest ROWID in the table prior to
> the insert."
>
> Butif I delete the max rowid I expect it to be re-used based on the 
> above

The quote you cite applies to the case where AUTOINCREMENT keyword is
not specified. But in your latest example, you do specify one.
--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record in db

[Igor Tandetnik]

On 8/22/2011 1:11 PM, Black, Michael (IS) wrote:
> Sure it does.
>
> sqlite>  select rowid,id,* from t1 where id=(select max(id) from t1);
> 4|4|4|two
>
>
>
> "two" was the last succesful insert in my example.

But 'another two' (id==2) was the last successful insert in my extension 
of your example. Yet your statement still returns 4.


> I'm now noticing though that rowid is not working as documented.
>
> http://www.sqlite.org/autoinc.html
>
> Says "If no ROWID is specified on the insert, or if the specified
> ROWID has a value of NULL, then an appropriate ROWID is created
> automatically. The usual algorithm is to give the newly created row a
> ROWID that is one larger than the largest ROWID in the table prior to
> the insert."
>
> Butif I delete the max rowid I expect it to be re-used based on the 
> above

The quote you cite applies to the case where AUTOINCREMENT keyword is 
not specified. But in your latest example, you do specify one.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record in db

[Black, Michael (IS)]

Sure it does.

sqlite> select rowid,id,* from t1 where id=(select max(id) from t1);
4|4|4|two



"two" was the last succesful insert in my example.





I'm now noticing though that rowid is not working as documented.





http://www.sqlite.org/autoinc.html

Says "If no ROWID is specified on the insert, or if the specified ROWID has a 
value of NULL, then an appropriate ROWID is created automatically. The usual 
algorithm is to give the newly created row a ROWID that is one larger than the 
largest ROWID in the table prior to the insert."



Butif I delete the max rowid I expect it to be re-used based on the 
above



sqlite> create table t1(id integer primary key autoincrement,data text);
sqlite> insert into t1 values(null,'one');
sqlite> insert into t1 values(null,'two');
sqlite> insert into t1 values(null,'three');
sqlite> select rowid,* from t1;
1|1|one
2|2|two
3|3|three
sqlite> delete from t1 where data='two';
sqlite> insert into t1 values(null,'two');
sqlite> select rowid,* from t1;
1|1|one
3|3|three
4|4|two
sqlite> delete from t1 where data='two';
sqlite> insert into t1 values(null,'two');
sqlite> select rowid,* from t1;
1|1|one
3|3|three
5|5|two<< This should be 4,5 and not 5,5 according to the docs as "3" was 
the largest in the table prior to insert.











Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Igor Tandetnik [itandet...@mvps.org]
Sent: Monday, August 22, 2011 11:49 AM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] Last record in db

On 8/22/2011 12:42 PM, Black, Michael (IS) wrote:
> autoincrement does keep order regardless of deletes.  rowid won't guarantee 
> it.
>
>
>
> So you don't "have" to add your own unless you need more than autoincrement.
>
>
>
> sqlite>  create table t1(id integer primary key,data text);
> sqlite>  insert into t1 values(NULL,'one');
> sqlite>  insert into t1 values(NULL,'two');
> sqlite>  insert into t1 values(NULL,'three');
> sqlite>  select rowid from t1 where rowid=(select max(rowid) from t1);
> 3
> sqlite>  delete from t1 where data='two';
> sqlite>  insert into t1 values(NULL,'two');
> sqlite>  select rowid from t1 where rowid=(select max(rowid) from t1);
> 4
>
>
>
> You'll always get the "last" record that was successfully inserted.

sqlite> insert into t1 values(2, 'another two');
sqlite> select rowid from t1 where rowid=(select max(rowid) from t1);
4

It seems that the record your statement returns is not the record that
was successfully inserted most recently.
--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record in db

[Igor Tandetnik]

On 8/22/2011 12:42 PM, Black, Michael (IS) wrote:
> autoincrement does keep order regardless of deletes.  rowid won't guarantee 
> it.
>
>
>
> So you don't "have" to add your own unless you need more than autoincrement.
>
>
>
> sqlite>  create table t1(id integer primary key,data text);
> sqlite>  insert into t1 values(NULL,'one');
> sqlite>  insert into t1 values(NULL,'two');
> sqlite>  insert into t1 values(NULL,'three');
> sqlite>  select rowid from t1 where rowid=(select max(rowid) from t1);
> 3
> sqlite>  delete from t1 where data='two';
> sqlite>  insert into t1 values(NULL,'two');
> sqlite>  select rowid from t1 where rowid=(select max(rowid) from t1);
> 4
>
>
>
> You'll always get the "last" record that was successfully inserted.

sqlite> insert into t1 values(2, 'another two');
sqlite> select rowid from t1 where rowid=(select max(rowid) from t1);
4

It seems that the record your statement returns is not the record that 
was successfully inserted most recently.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record in db

[Stephan Beal]

On Mon, Aug 22, 2011 at 6:42 PM, Black, Michael (IS)  wrote:

> You'll always get the "last" record that was successfully inserted.
>

Just to play devil's advocate for a moment...

As i recall, someone posted a report on this list a few months ago to report
that the "last" insert ID (sqlite3_last_insert_rowid()) in his case was the
ID of an insert (in a different table) caused as a side-effect of an
on-insert trigger in the original target table.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record in db

[Black, Michael (IS)]

autoincrement does keep order regardless of deletes.  rowid won't guarantee it.



So you don't "have" to add your own unless you need more than autoincrement.



sqlite> create table t1(id integer primary key,data text);
sqlite> insert into t1 values(NULL,'one');
sqlite> insert into t1 values(NULL,'two');
sqlite> insert into t1 values(NULL,'three');
sqlite> select rowid from t1 where rowid=(select max(rowid) from t1);
3
sqlite> delete from t1 where data='two';
sqlite> insert into t1 values(NULL,'two');
sqlite> select rowid from t1 where rowid=(select max(rowid) from t1);
4



You'll always get the "last" record that was successfully inserted.





Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Simon Slavin [slav...@bigfraud.org]
Sent: Monday, August 22, 2011 11:32 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] EXT :Re: Last record in db


On 22 Aug 2011, at 5:13pm, Igor Tandetnik wrote:

> On 8/22/2011 9:52 AM, Black, Michael (IS) wrote:
>> No...if you use autoincrement you can guarantee that "last" will be the last 
>> record inserted.
>
> There's no contradiction. "Last" is still defined only for ordered sets
> - you just chose a particular ordering, by rowid.

And even if you do that, it's easy to break the expected ordering:

> .schema t1
CREATE TABLE t1(id INT,data TEXT);

> select * from t1 ;
id  data
--  --
1   one
2   two
3   tre

> delete from t1 where data = 'two';

> insert into t1 (it, data) values (2, 'second');

Now the 'last' record is not the one with the highest value in the id column.

Igor is right.  The question from the original poster doesn't mean anything in 
SQL.  SQL has no concept of an order for rows, so it has no idea which row is 
'first' or 'last'.  If you, the programmer have your own idea what is first or 
last, write your own numbers into the database.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record in db

[Igor Tandetnik]

On 8/22/2011 10:04 AM, Brad Stiles wrote:
> What happens when you do:
>
> select * from t1 where rowid = (select max( rowid ) from t1);
>
> or
>
> select * from t1 where rowid in (select max( rowid ) from t1);

or

select * from t1 order by rowid desc limit 1;

Likely more efficient this way.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record in db

[Black, Michael (IS)]

sqlite3 does NOT guarantee rowid always increments and never gives FULL return 
(at least according to the docs).



autoincrement does.

http://www.sqlite.org/autoinc.html



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Stephan Beal [sgb...@googlemail.com]
Sent: Monday, August 22, 2011 9:07 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Last record in db

On Mon, Aug 22, 2011 at 4:01 PM, Cousin Stanley <cousinstan...@gmail.com>wrote:

> > select * from t1 where rowid = max( rowid ) ;
> Error: misuse of aggregate function max()
>

That can be rewritten as:

> select * from t1 order by rowid desc limit 1;

sqlite3 guarantees that the rowid only increments, never decrements. If the
rowid limit is ever hit (very unlikely to happen!) you'll get a "db full"
error, in which case it's probably time to recreate the table to get the
rowid to start counting at 1 again.

--
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record in db

[Stephan Beal]

On Mon, Aug 22, 2011 at 4:01 PM, Cousin Stanley wrote:

> > select * from t1 where rowid = max( rowid ) ;
> Error: misuse of aggregate function max()
>

That can be rewritten as:

> select * from t1 order by rowid desc limit 1;

sqlite3 guarantees that the rowid only increments, never decrements. If the
rowid limit is ever hit (very unlikely to happen!) you'll get a "db full"
error, in which case it's probably time to recreate the table to get the
rowid to start counting at 1 again.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record in db

[Brad Stiles]

What happens when you do:

select * from t1 where rowid = (select max( rowid ) from t1);

or

select * from t1 where rowid in (select max( rowid ) from t1);



On Mon, Aug 22, 2011 at 10:01 AM, Cousin Stanley
 wrote:
>
> Black, Michael (IS) wrote:
>
>> select * from table where rowid=max(rowid);
>
> $ sqlite3 m2d1.sql3
> -- Loading resources from /home/sk/.sqliterc
> SQLite version 3.7.3
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
>
>> .tables
> t1  t2  t3
>
>> .schema t1
> CREATE TABLE t1(id INT,data TEXT);
>
>> select * from t1 ;
> id          data
> --  --
> 1           one
> 2           two
> 3           tre
>
>> select * from t1 where rowid = max( rowid ) ;
> Error: misuse of aggregate function max()
>
>> select max( rowid ) from t1 ;
> max( rowid )
> 
> 3
>
>
> --
> Stanley C. Kitching
> Human Being
> Phoenix, Arizona
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record in db

[Cousin Stanley]

Black, Michael (IS) wrote:

> select * from table where rowid=max(rowid);

$ sqlite3 m2d1.sql3
-- Loading resources from /home/sk/.sqliterc
SQLite version 3.7.3
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

> .tables
t1  t2  t3

> .schema t1
CREATE TABLE t1(id INT,data TEXT);

> select * from t1 ; 
id  data  
--  --
1   one   
2   two   
3   tre   

> select * from t1 where rowid = max( rowid ) ; 
Error: misuse of aggregate function max()

> select max( rowid ) from t1 ; 
max( rowid )

3 


-- 
Stanley C. Kitching
Human Being
Phoenix, Arizona


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record in db

[Igor Tandetnik]

sreekumar...@gmail.com wrote:
> Is 'last' valid only for 'ordered' set of records?

It would be more precise to say that an ordering induces GetLastRecord 
function, and vice versa. If you have a total ordering, then the "last" record 
is the one that compares greater than all others in this ordering.

In the other direction, if you have GetLastRecord(set_of_records) function 
defined somehow, then you can pick the last record, remove it from the set, 
pick the last of the remaining, remove that one from the set, and so on. This 
process generates a total ordering of the set.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record in db

[sreekumar . tp]

Is 'last' valid only for 'ordered' set of records?

--Original Message--
From: Igor Tandetnik
Sender: sqlite-users-boun...@sqlite.org
To: sqlite-users@sqlite.org
ReplyTo: General Discussion of SQLite Database
Subject: Re: [sqlite] Last record in db
Sent: Aug 22, 2011 18:51

sreekumar...@gmail.com wrote:
> Let's say there is a table in a db that holds a few thousands of records..
> Records are inserted and deleted from the table. At any given point I should 
> be able to retrieve the 'last' record..

Last by what ordering?

>  'Last' is probably the record which is stored at the node with max depth?

What's a "node" or a "depth" in this context?
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Sent from BlackBerry® on Airtel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record in db

[Igor Tandetnik]

sreekumar...@gmail.com wrote:
> Let's say there is a table in a db that holds a few thousands of records..
> Records are inserted and deleted from the table. At any given point I should 
> be able to retrieve the 'last' record..

Last by what ordering?

>  'Last' is probably the record which is stored at the node with max depth?

What's a "node" or a "depth" in this context?
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record in db

[sreekumar . tp]

Let's say there is a table in a db that holds a few thousands of records..
Records are inserted and deleted from the table. At any given point I should be 
able to retrieve the 'last' record..

 'Last' is probably the record which is stored at the node with max depth?


--Original Message--
From: Igor Tandetnik
Sender: sqlite-users-boun...@sqlite.org
To: sqlite-users@sqlite.org
ReplyTo: General Discussion of SQLite Database
Subject: Re: [sqlite] Last record in db
Sent: Aug 22, 2011 18:33

sreekumar...@gmail.com wrote:
> 1.What's the fastest way to retrieve the last record in the DB.

a) Define "last". b) From which table in the DB?

> 2. Sqlite3_step takes you to the next record. Is there an equivalent for 
> navigating backwards ?

Not really, but see http://www.sqlite.org/cvstrac/wiki/wiki?p=ScrollingCursor
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Sent from BlackBerry® on Airtel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record in db

[Black, Michael (IS)]

Or if you are talking about some specific select statement.



select * from table order by mystuff desc;





Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of sreekumar...@gmail.com [sreekumar...@gmail.com]
Sent: Monday, August 22, 2011 7:51 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Last record in db

Hi,

1.What's the fastest way to retrieve the last record in the DB.
2. Sqlite3_step takes you to the next record. Is there an equivalent for 
navigating backwards ?
Sent from BlackBerry® on Airtel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record in db

[Igor Tandetnik]

sreekumar...@gmail.com wrote:
> 1.What's the fastest way to retrieve the last record in the DB.

a) Define "last". b) From which table in the DB?

> 2. Sqlite3_step takes you to the next record. Is there an equivalent for 
> navigating backwards ?

Not really, but see http://www.sqlite.org/cvstrac/wiki/wiki?p=ScrollingCursor
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record in db

[Black, Michael (IS)]

Oh...can I guess?



select * from table where rowid=max(rowid);





Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of sreekumar...@gmail.com [sreekumar...@gmail.com]
Sent: Monday, August 22, 2011 7:51 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Last record in db

Hi,

1.What's the fastest way to retrieve the last record in the DB.
2. Sqlite3_step takes you to the next record. Is there an equivalent for 
navigating backwards ?
Sent from BlackBerry® on Airtel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Last record in db

[sreekumar . tp]

Hi,

1.What's the fastest way to retrieve the last record in the DB.
2. Sqlite3_step takes you to the next record. Is there an equivalent for 
navigating backwards ?
Sent from BlackBerry® on Airtel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users