getting the last ID in db

[Shaun Perry]

Hi

I have been using the following code to get the last id in my database
tables:

<cfquery name = "getid" datasource = "#SESSION.clientdatasource#">
SELECT @@IDENTITY FROM tbl_templates as lastadded
</cfquery>

This works fine if there are records, but if the recordcount is 0 i do not
get a value, is there another way to do this? I am using MySQL.

Cheers in advance!

Shaun

[BKBK]

It could be as a result the peculiarity of @@IDENTITY, that
it applies to all your tables, not just to one, and to every scope
of each table. Therefore, the result you obtain could have been
caused, for example, by some trigger-scope change
in another table. I should experiment with the following function
instead

SELECT IDENT_CURRENT('tbl_templates')

[mxstu]

Assuming you are talking about MS SQL (SQL Server) not mySQL ....

Are you trying to retrieve a new record ID after an INSERT statement? If so,
are you wrapping both the INSERT and SELECT @@IDENTITY inside a CFTRANSACTION?

If you just want to retrieve the last ID in your tables, you probably want

- SELECT MAX(IDColumn) FROM yourTable

and not

- SELECT @@IDENTITY

Maybe I am misunderstanding you, but in most cases you would not use
@@IDENTITY to retrieve the last ID

[The Law]

Yes, I use SELECT MAX(ID column) FROM table all the time. If max ID is 0, then that's what I get.

[Marc E]

This is gonna sound lame b/c i have no proof of this b/c it's been about a
year, but....

i set up a simultaneous user test using apache JMeter and had 10 users every
10 seconds hit a CF page. One of the queries used @@Identity, the other used
Ident_current(). I thought that ident_current() was what i needed, but i
wanted to verify it.

I don't remember all the details, but I do remember that ident_current was
definitely not what I needed, and that @@Identity worked correctly.

you could do something like select (Case when isnull(@@identity) then 0 else
@@identity end) as ID (syntax is probably wrong, but that's the Idea). that
might get you what you need.



"BKBK" <webforumsuser@macromedia.com> wrote in message
news:dc0r0r$isc$1@forums.macromedia.com...

> It could be as a result the peculiarity of @@IDENTITY, that
> it applies to all your tables, not just to one, and to every scope
> of each table. Therefore, the result you obtain could have been
> caused, for example, by some trigger-scope change
> in another table. I should experiment with the following function
> instead
>
> SELECT IDENT_CURRENT('tbl_templates')
>
>
>