Thank you Rick! That was really helpful!
I need to use option 2, since i cannot retrieve automatically generated identity numbers on a multi-columns insert (https://issues.apache.org/jira/browse/DERBY-3609?page=com.atlassian.jira.plugin.system.issuetabpanels%3Aall-tabpanel). that select you posted is exactly what I need in order to maintain the identities myself for the time being, starting with the last one (+ inc size) every time the program restarts (or the connection was lost). Rick Hillegas-2 wrote: > > Hi Amir, > > 1) If what you need to know is the max value on disk right now, then you > will need to issue a "select max(...)" statement. > > 2) Maybe, however, all you need to know is the max value that was ever > on disk. Of course, this can be a different number than (1) because you > may have deleted rows at the upper end. > > If all you need to know is (2), then you can get the answer from the > system catalogs. Here's a little script which creates a table with an > identity column, inserts and deletes some rows, and then selects the max > value that was ever in the identity column. The last query gives you the > answer to (2): > > drop table s; > create table s > ( > a int generated always as identity (start with 3, increment by 3), > b int > ); > > insert into s( b ) values ( 1 ), ( 2 ), ( 3 ), ( 4 ); > delete from s where b = 4; > insert into s( b ) values ( 11 ), ( 12 ), ( 13 ), ( 14 ); > > select * from s order by b; > > -- now find the max value that was ever put in the identity column > select c.autoincrementvalue - c.autoincrementinc > from sys.syscolumns c, sys.systables t > where t.tablename = 'S' > and t.tableid = c.referenceid > and c.columnname = 'A'; > > For more information, please see the Reference Guide section describing > the SYSCOLUMNS system catalog. > > Hope this helps, > -Rick > > Amir Michail wrote: >> Hi, >> >> I was wondering what is the most efficient way to get the max >> automatically generated row id. >> >> select max(...) is slow. >> >> Amir >> >> > > > -- View this message in context: http://www.nabble.com/Most-efficient-way-to-get-max-row-id--tp20060117p22700688.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
