Re: [sqlite] Last record in db

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.

Re: [sqlite] Last record in db

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 Subje

Re: [sqlite] Last record in db

: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 t

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

Re: [sqlite] Last record in db

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

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

Re: [sqlite] Last record in db

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 th

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,

Re: [sqlite] Last record in db

...@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|t

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

Re: [sqlite] Last record in db

___ 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: >

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>

Re: [sqlite] Last record in db

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

Re: [sqlite] Last record in db

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

Re: [sqlite] Last record in db

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. --

Re: [sqlite] Last record in db

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

Re: [sqlite] Last record in db

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

Re: [sqlite] Last record in db

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

Re: [sqlite] Last record in db

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

Re: [sqlite] Last record in db

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

Re: [sqlite] Last record in db

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

Re: [sqlite] Last record in db

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

Re: [sqlite] Last record in db

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

Re: [sqlite] Last record in db

...@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

Re: [sqlite] Last record in db

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

Re: [sqlite] Last record in db

...@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

[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