Re: [sqlite] EXT :Re: Last record in db

[Igor Tandetnik]

sreekumar...@gmail.com wrote:
> How does sqlite insert a record ? More specifically how does sqlite update 
> the B-tree with the new record . Is there a linkage
> made between the newly inserted record and the previous one ? 

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

-- 
Igor Tandetnik

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

Re: [sqlite] EXT :Re: Last record in db

[sreekumar . tp]

I understand now. 

How does sqlite insert a record ? More specifically how does sqlite update the 
B-tree with the new record . Is there a linkage made between the newly inserted 
record and the previous one ?
Sent from BlackBerry® on Airtel

-Original Message-
From: Simon Slavin <slav...@bigfraud.org>
Sender: sqlite-users-boun...@sqlite.org
Date: Mon, 22 Aug 2011 17:32:51 
To: General Discussion of SQLite Database<sqlite-users@sqlite.org>
Reply-To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
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] EXT :Re: Last record in db

[Simon Slavin]

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

Re: [sqlite] EXT :Re: Last record in db

[Igor Tandetnik]

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

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

Re: [sqlite] EXT :Re: Last record in db

[Brad Stiles]

On Mon, Aug 22, 2011 at 10:08 AM, Black, Michael (IS)
 wrote:
> Brad got it:

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

> sqlite> select * from t1 where rowid = (select max(rowid) from t1);
> 3|three
>
> Why is max(rowid) a "misuse".  Seems perfectly logical to me.  Not for an 
> update but should work for select.

I'm guessing that "max(rowid)" all by itself is either evaluating
"rowid" as a variable or value independent of a table, or failing
entirely because there is no context for evaluating rowid in the
failing instance.

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

Re: [sqlite] EXT :Re: Last record in db

[Black, Michael (IS)]

Brad got it:



sqlite> select * from t1 where rowid = (select max(rowid) from t1);
3|three



Why is max(rowid) a "misuse".  Seems perfectly logical to me.  Not for an 
update but should work for select.



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 Brad Stiles [bradley.sti...@gmail.com]
Sent: Monday, August 22, 2011 9:04 AM
To: General Discussion of SQLite Database
Subject: EXT :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
<cousinstan...@gmail.com> 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] EXT :Re: Last record in db

[Black, Michael (IS)]

No...if you use autoincrement you can guarantee that "last" will be the last 
record inserted.



So "select * from mytable where myid=max(myid)" will work where myid is 
autoincrement.



The normal rowid will work also as long as you don't delete the max(rowid) and 
you don't insert more than 9,223,372,036,854,775,807 rows.



select * from mytable where rowid=max(rowid)



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 sreekumar...@gmail.com [sreekumar...@gmail.com]
Sent: Monday, August 22, 2011 8:28 AM
To: General Discussion of SQLite Database
Subject: EXT :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...@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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users