Question
Jairton Junior · Feb 19, 2021

Is there a "SELECT LAST_SERIAL()"?

When using "IDENTITY" as my primary key, I can select the last inserted ID with 

SELECT LAST_IDENTITY() FROM %TSQL_sys.snf;

Actually this is how Hibernate + Iris' Driver acquires the inserted ID when mapping with

@GeneratedValue(strategy = GenerationType.IDENTITY)

 

Now, considering that I am using the type "SERIAL" as my primary key instead, how can I get the last inserted ID?

Note that with "SERIAL" I can forcefully insert any value for this ID, from which Iris will continue generating values...

CREATE TABLE test (
    test_id serial NOT NULL,
    name varchar(100) NULL,
    CONSTRAINT pk_test PRIMARY KEY (test_id)
);

INSERT INTO test (test_id, name) values (10, 'test 10'); --inserted "test_id" value: 10
SELECT LAST_IDENTITY() FROM %TSQL_sys.snf; --returned value: 1

INSERT INTO test (name) values ('test 11'); --generated "test_id" value: 11
SELECT LAST_IDENTITY() FROM %TSQL_sys.snf; --returned value: 2

Also, how could I map this kind of ID using Hibernate? I was thinking about implementeing an "IdentifierGenerator", but is there a simpler solution?

$ZV: IRIS for UNIX (Ubuntu Server LTS for x86-64 Containers) 2020.3 (Build 221U) Thu Oct 1 2020 11:24:59 EDT
Product version:
IRIS 2020.3
00
1 0 1 44

Replies

#1 there is no "SELECT LAST_SERIAL()" 

#2 depending what your expectation on LAST is I see 2 possible workarounds
#2A LAST = max:    "SELECT MAX(test_id) FROM test
#2B LAST = latest: "SELECT test_id, MAX(ID) FROM test GROUP BY  2"

#2B is based on the fact that you always have an  auto-incremented ID and it's a shortcut of
"SELECT test_id  FROM test where id = (SELECT max(ID) from junior.test)"
or
"SELECT TOP 1 test_id FROM test WHERE ID < 999999999999999999 ORDER BY ID DESC