Posted by David Portas on 08/04/06 19:00

Jack Turnbull wrote:
> Hi,
> I am writing code for the subject version and am trying to find the
> equivalent of
>
> SELECT SaveDate FROM Performance
> WHERE IDENTITYCOL = IDENT_CURRENT('Performance')
>
> In other words I need to return a field in the last row of a table. v7.0
> does not recognise IDENT_CURRENT
>
> Any offers?
>
> (btw, cannot upgrade - not my database)
>
> Thanks

DBCC CHECKIDENT ('tablename', NORESEED) will give you the same value as
IDENT_CURRENT() but that won't necessarily be the last value inserted
in the current connection and it may not even be a value that exists in
the table. Those same caveats apply to IDENT_CURRENT(). IDENT_CURRENT()
is of very little use at all IMO but it is often MIS-used.

Porbably the better alternatives are:

SELECT TOP 1 SaveDate
FROM Performance
ORDER BY identitycol DESC;

SELECT SaveDate
FROM Performance
WHERE identitycol = @@IDENTITY;

In SQL Server 2000, use SCOPE_IDENTITY() in preference to @@IDENTITY.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

[Back to original message]