September 17, 2003 at 9:22 am
Hello,
Without using cursors, what is the best way to retrieve the last record within a table?
Thanks,
Jenise
September 17, 2003 at 9:26 am
Hi Jenise,
quote:
Without using cursors, what is the best way to retrieve the last record within a table?
what exactly do you mean by 'last record'?
There isn't such a thing as the last record in SQL.
If you mean the 'last record' with respect to a certain column you could use something like
SELECT TOP 1 <your_fields> FROM <your_table> ORDER BY <your_fields> DESC
If you mean the last inserted on a table that has an indentity column, I think @@IDENTITY will give you the information
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
September 17, 2003 at 9:40 am
Actually, I was looking for the last record that had been inserted with an identity column.
Thank You!
Jenise
September 17, 2003 at 10:29 am
SELECT MAX(myid)
FROM mytable
Replace myid with whatever column name is the ID column and replace mytable with the proper table name.
-SQLBill
September 17, 2003 at 10:38 am
Or to retrieve the last record by myid issue:
select * from mytable where myid = (select max(myid) from mytable)
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 18, 2003 at 4:23 am
Sorry for butting in!
If you are dealing with last value on IDENTITY columns I would suggest using:
IDENT_CURRENT( <table_name> )
According to my book it is always the fastest one. According to server traces it generates the least amounts of reads (IDENT_CURRENT is generating a 'Internal Scan of Constants instead of reading from the table) and does not touch the table in question.
BOL:
quote:
IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
Ex:
SELECT *
FROM <table_name>
WHERE <identity_column> = IDENT_CURRENT('<table_name>')
Regards, Hans!
Edit: Didn't respond to the question 😛
Edited by - hanslindgren on 09/18/2003 06:10:15 AM
September 18, 2003 at 6:45 am
quote:
Sorry for butting in!If you are dealing with last value on IDENTITY columns I would suggest using:
IDENT_CURRENT( <table_name> )
According to my book it is always the fastest one. According to server traces it generates the least amounts of reads (IDENT_CURRENT is generating a 'Internal Scan of Constants instead of reading from the table) and does not touch the table in question.
BOL:
quote:
IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.Ex:
SELECT *
FROM <table_name>
WHERE <identity_column> = IDENT_CURRENT('<table_name>')
Regards, Hans!
Yes, but...
create table test(id smallint identity)
insert test default values
begin tran
insert test default values
rollback
select max(id), ident_current('test') from test
--Jonathan
--Jonathan
September 18, 2003 at 7:36 am
Jonathan:
Thanx, I didn't think about the possibility that it might not be transaction safe.
Regards, Hans!
September 18, 2003 at 12:17 pm
Hans,
Still...nice trick! And if you know there are no deletions occuring on the table then this will work.
This is what I use:
SELECT top 1 *
FROM <table name>
order by <identity column> desc
This way you don't have to use a subquery to get the rest of the row, like below:
select * from BatchDetail
where batchid = (SELECT max(BDid)
FROM batchdetail)
Also, doing a top 1 on a column with a clustered index can be significantly faster than max() when working with more than a million records (even though the execution plan shows it being the same):
if object_ID('tempdb..#temp') is not null drop table #Temp
go
Create Table #temp (col1 int identity Primary Key, Note varchar(100))
While 1=1
Begin
Insert #temp Values ('This is filler')
IF @@Identity = 1000000 break
END
select top 1 Col1
from #temp
order by col1 desc
select max(Col1)
from #temp
Signature is NULL
September 18, 2003 at 6:12 pm
Generally speaking,
Using SCOPE_IDENTITY() function is much better than @@IDENTITY
because the table you inserted might have triggers and triggers may insert data to other tables having identity columns.
So You get the idetity value of the table that trigger inserted by using @@IDENTITY
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply