5

i am working with HSQL database for testing purpose. i want standalone db file. but now i am in trouble to get last inserted row id (auto-incremental - identity) in HSQL. how can i get id??

Ankur Loriya
  • 3,276
  • 8
  • 31
  • 58

2 Answers2

22

http://www.hsqldb.org/doc/guide/ch09.html

the last inserted value into an identity column for a connection is available using the function IDENTITY(), for example (where Id is the identity column):

INSERT INTO Test (Id, Name) VALUES (NULL,'Test');
CALL IDENTITY();

ryanjustus
  • 221
  • 2
  • 3
  • The link in this answer is out of date. Info on Identity() can now be found in chapter 10 here: http://www.hsqldb.org/doc/guide/builtinfunctions-chapt.html#bfc_system_functions – Matthieu Cormier Dec 10 '19 at 19:18
-5

It's pretty hard to write a query to perform this when you haven't given your table schema, but something like the following:

SELECT TOP 1 Id FROM [TABLENAME] ORDER BY Id DESC
weenoid
  • 1,156
  • 2
  • 11
  • 24
  • While this may work, it would be hard to imagine a more expensive way to get the generated identity. Moreover, if rows are deleted from a table, there is no guarantee that the next identity value generated will always be the largest. – scottb Apr 17 '13 at 23:16
  • "there is no guarantee that the next identity value generated will always be the largest" - Regardless of the method used to obtain the id of the last row inserted you will always encounter this problem. – weenoid Apr 18 '13 at 12:59
  • Prefer IDENTITY() (see ryanjustus' answer). Using the max ID of the table would return a wrong result if other concurrent transactions also insert data. – Adrien Sep 27 '13 at 12:40