Getting the net id (primary key)

[François]

Hello,

I need a way to jet the current highest key (an integer) in order to generate the primary ley of the next insert.
In SQL, this looks like:

Code:

Select max(id) from aTable

How can I do the same with RebDB?

Thanks

François

[Ashley]

RebDB does not support aggregate functions like MAX, MIN, SUM, COUNT, STD, AVG ... yet. In the meantime, MAX can be emulated with rowid as in the following code:

Code:

db-echo false
db-insert my-table compose [(1 + first db-select/where id my-table [rowid = last]) 1-Jan-2000 "Note-A"]

This technique works as INSERT appends data to the table, so the last row should contain the greatest value. If, however, you UPDATE intermediate rows with a greater value then this technique needs to be replaced with a more expensive sub-select as in:

Code:

db-echo false
db-insert my-table compose [(1 + last db-select/order id my-table id) 1-Jan-2000 "Note-B"]

But this does not scale well.

The next version of RebDB provides a shortcut ('next) to do exactly what the first code snippet does.

[Ashley]

As an addendum to my previous post, note that as of RebDB v1.2 you can always use rowid as the primary key with statements like:

Code:

db-select/where rowid my-table [...]
db-select/where [rowid name address] ...
db-select/where * my-table [rowid = ...]

but this assumes you do not need to delete / update the key, although I have a client who uses rowid as a Patient# with a status column indicating whether the record is active or not (as they are not allowed to delete patient records by law).

[François]

Thanks,

I was already using the first approche (first db-select ....).
But, I am keeping the next key in cache, so I only have to execute this select once at the lauch of the application.

Again, congratulation for your lib

Regards

François