sqlite the last id how get ?

[faustf]

hi guy

but what command i must use for take the last id of table in sqlite ??

[faustf]

i must use query with this SELECT LAST(column_name) FROM table_name ? ?? ? ?

[jchd]

There are two posibilities but they are not equivalent:

select max(id) from mytable -- will get the ... maximum value of ID unsurprisingly

Use of _SQLite_LastInsertRowID($hDB) will return the last inserted rowid on this connection. Use that right after an insert or replace statement. Here, note that replace is actually "insert or replace" and if the row already exist, it amounts to a delete then an insert, thus changing the rowid if that column is an integer primary key autoincrement.

Anecdotically you can also do:

select id from mytable order by id desc limit 1

but this is the same as the first solution above (using max(id)).

Edited April 17, 2013 by jchd

[Nessie]

If you want to know the ROWID of the most recent insert in the database just use _SQLite_LastInsertRowID. If you want to know the last numerical ID of a specific table just do that:

SELECT MAX(id) FROM table_name

Or other very ungly code:

SELECT LAST (id) AS LastID FROM table_name

OR

SELECT id FROM table_name ORDER BY id DESC LIMIT 1

Hi!

[jchd]

Note you both that LAST() is not valid for SQLite, unless part of a loaded extension.

[Nessie]

Note you both that LAST() is not valid for SQLite, unless part of a loaded extension.

That's true

Hi!

[faustf]

hi thankz for answer ,

why when i use _SQLite_LastInsertRowID($DB) this return 0 ???

i insert like in example (for understund but nothing )

is probably give 0 because this day dont insert nothing ???

[faustf]

so i try the solution in this mode

SELECT id FROM table_name ORDER BY id DESC LIMIT 1

i create this script

Func _lettura_anagrafica()
_SQL_Startup()
  $Db=_SQLite_Open($percorso_Db); Open a permanent disk database
If @error Then
  MsgBox(16+262144, "SQLite Error", "Non posso Aprire il Database!")
  ;Exit -1
EndIf

;_SQLite_Query($Db, "SELECT ana_id FROM anagrafica ORDER BY ana_id DESC LIMIT 1;", $hQuery)
;_SQLite_FetchData($hQuery, $last_aRow)
;MsgBox(0,'lultima',$last_aRow[0])

    Local $aRow, $iRows, $iCols
    _SQLite_GetTable2d($Db, "SELECT * FROM anagrafica  ORDER BY ana_id DESC LIMIT 1;", $aRow, $iRows, $iCols)
Local $num_righe= 0,$v=4,$h=1
Do
_GUICtrlListView_AddItem($List1AC, $arow[$h][1],  $num_righe) ;nome ditta
_GUICtrlListView_AddSubItem($List1AC,  $num_righe, $arow[$h][8], 1, 1) ; mail
_GUICtrlListView_AddSubItem($List1AC,  $num_righe, $arow[$h][7], 2, 2) ; telefono
_GUICtrlListView_AddSubItem($List1AC,  $num_righe, $arow[$h][9], 3, 3) ; cell
_GUICtrlListView_AddSubItem($List1AC,  $num_righe, $arow[$h][2], 4, 4) ; indirizzo
_GUICtrlListView_AddSubItem($List1AC,  $num_righe, $arow[$h][3], 5, 5) ; citta
_GUICtrlListView_AddSubItem($List1AC,  $num_righe, $arow[$h][10], 6, 6) ; fax
_GUICtrlListView_AddSubItem($List1AC,  $num_righe, $arow[$h][0], 7, 7) ; coddb
$h=$h+1
$num_righe = $num_righe +1
$v=$v-1
Until $v=0
_SQLite_Close()
_SQLite_Shutdown()

EndFunc

but if insert

ORDER BY id DESC LIMIT 1

give me this error

:\Users\stefano\Documents\programmazione\SANDRO GEST\sandro-gest.au3 (406) : ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.:
_GUICtrlListView_AddItem($List1AC, $arow[$h][1],  $num_righe)
_GUICtrlListView_AddItem($List1AC, ^ ERROR
->17:30:49 AutoIT3.exe ended.rc:1

[kylomas]

faustf,

Returns the ROWID of the most recent insert in the database by this connection

Have you inserted a row during the life of the current connection?

kylomas

edit: response to post #7

Edited April 17, 2013 by kylomas

[Nessie]

From the help file:

Returns the ROWID of the most recent insert in the database by this connection

If you for example insert a new item in the database:

_SQLite_Open() 
_SQLite_Exec(-1, "INSERT INTO persons VALUES ('Alice','43');")

When you call _SQLite_LastInsertRowID it will tell to you the id of he last INSERT in the database, in this case the last ID of the table "persons".

P.S. Use the edit button instead of add a new post

Hi!

Edited April 17, 2013 by Nessie

[kylomas]

faustf,

This is not valid for an insert stmt

ORDER BY id DESC LIMIT 1

Are you checking return codes from your SQLite calls, and/or, running this using the full SCITE editor?

kylomas

[faustf]

no not insert new in this connection therfore not use _SQLite_LastInsertRowID($DB) and use

"SELECT ana_id FROM anagrafica ORDER BY ana_id DESC LIMIT 1;

[faustf]

a ok kilomas i must do 2 routine ok thankz i try

[kylomas]

faustf,

If you execute the script from the editor (F5) you will see any SQL errors in the console area.

kylomas

[jchd]

As I said, _SQLite_LastInsertRowID returns the value of the last inserted row ID within the current connection. That value is not stored in the DB so if you close the connection, it's lost forever.

Sorry, I was distracted and my post now sounds retarded.

@faustf,

Again, don't _SQLite_Open and *_Close in every function, only at program startup and termination.

[faustf]

yea understund therfore dont wanna use _SQLite_LastInsertRowID but use order by etc...... now try to do and soon write

[kylomas]

faustf,

Response to post #13

a ok kilomas i must do 2 routine ok thankz i try

No, you can use a construct like this

if _sqlite_exec(-1,'some SQL stmt...;') <> $sqlite_ok then do your_error_routine()

kylomas

[jchd]

select max(id) from sometable

is more concise and will do as well.

Please note that this does not garanty that you'll get the last [inserted] row id. Autoincrement may exhaust the largest >0 64-bit value and select any unused random value.

[faustf]

i try in this mode and go

Func _lettura_anagrafica()
_SQL_Startup()
  $Db=_SQLite_Open($percorso_Db); Open a permanent disk database
If @error Then
  MsgBox(16+262144, "SQLite Error", "Non posso Aprire il Database!")
  ;Exit -1
EndIf
_SQLite_Query($Db, "SELECT ana_id FROM anagrafica ORDER BY ana_id DESC LIMIT 1;", $hQuery)
While _SQLite_FetchData($hQuery, $1aRow) = $SQLITE_OK
    $sMsg &= $1aRow[0]
WEnd

    Local $aRow, $iRows, $iCols
    _SQLite_GetTable2d($Db, "SELECT * FROM anagrafica;", $aRow, $iRows, $iCols)
Local $num_righe= 0,$v=$sMsg,$h=1
Do
_GUICtrlListView_AddItem($List1AC, $arow[$h][1],  $num_righe) ;nome ditta
_GUICtrlListView_AddSubItem($List1AC,  $num_righe, $arow[$h][8], 1, 1) ; mail
_GUICtrlListView_AddSubItem($List1AC,  $num_righe, $arow[$h][7], 2, 2) ; telefono
_GUICtrlListView_AddSubItem($List1AC,  $num_righe, $arow[$h][9], 3, 3) ; cell
_GUICtrlListView_AddSubItem($List1AC,  $num_righe, $arow[$h][2], 4, 4) ; indirizzo
_GUICtrlListView_AddSubItem($List1AC,  $num_righe, $arow[$h][3], 5, 5) ; citta
_GUICtrlListView_AddSubItem($List1AC,  $num_righe, $arow[$h][10], 6, 6) ; fax
_GUICtrlListView_AddSubItem($List1AC,  $num_righe, $arow[$h][0], 7, 7) ; coddb
$h=$h+1
$num_righe = $num_righe +1
$v=$v-1
Until $v=0
_SQLite_Close()
_SQLite_Shutdown()
EndFunc

How do you would have done

[Nessie]

It all about you, you can use:

SELECT ana_id FROM anagrafica ORDER BY ana_id DESC LIMIT 1;

OR

SELECT MAX(ana_id) FROM anagrafica

P.S. The next time use the [autoit][/autoit] tag to post your code. La prossima volta usa il tag autoit così il tutto è più leggibile. Ciao!