Safest way to get last record ID from a table

Question

In SQL Server 2008 and higher what is the best/safest/most correct way

to retrieve the ID (based on autoincrementing primary key) out of the database table?
to retrieve the value of the last row of some other column (like, SELECT TOP 1 FROM Table ORDER BY DESC)?

A slight complication is whether you want the last ID issued that is still in the database, or the last ID issued - the record could of been added and removed. In which case using a max / top etc will fail to get you that id. — Andrew, Aug 06 '10 at 11:57

score 28 · Answer 1 · edited May 01 '20 at 22:21

28

SELECT IDENT_CURRENT('Table')

You can use one of these examples:

SELECT * FROM Table 
WHERE ID = (
    SELECT IDENT_CURRENT('Table'))

SELECT * FROM Table
WHERE ID = (
    SELECT MAX(ID) FROM Table)

SELECT TOP 1 * FROM Table
ORDER BY ID DESC

But the first one will be more efficient because no index scan is needed (if you have index on Id column).

The second one solution is equivalent to the third (both of them need to scan table to get max id).

edited May 01 '20 at 22:21

StormsEngineering

656
1
6
18

answered Aug 06 '10 at 08:47

gyromonotron

1,101
9
13

second one is wrong, If any rows deleted that will give wrong answer. – Aug 13 '14 at 11:30

score 27 · Answer 2 · answered Aug 06 '10 at 08:42

27

1.  SELECT MAX(Id) FROM Table

answered Aug 06 '10 at 08:42

Michael Pakhantsov

24,855
6
60
59

1

Thanks! best, just one :) – KingRider Jun 17 '15 at 14:34

score 19 · Accepted Answer · answered Aug 06 '10 at 12:01

19

Safest way will be to output or return the scope_identity() within the procedure inserting the row, and then retrieve the row based on that ID. Use of @@Identity is to be avoided since you can get the incorrect ID when triggers are in play.

Any technique of asking for the maximum value / top 1 suffers a race condition where 2 people adding at the same time, would then get the same ID back when they looked for the highest ID.

answered Aug 06 '10 at 12:01

Andrew

26,629
5
63
86

1

I read this article http://msdn.microsoft.com/en-us/library/ms190315.aspx about @@identity and scope_identity and it seems your answer is the most appropriate one. I have two additional questions before accepting the answer - if there is no insert before and we request scope_identity(), will it return anything? And another, if I was to create business keys in my application and then store them into the table, and I want them do derive from identity, is it fine to query for scope_identity()? – mare Aug 07 '10 at 00:57
If you fail to insert anything scope_identity() will return null; once you have returned scope_identity() and have the identity field, you can use it in queries to return to that row for whatever purposes you decide. – Andrew Aug 07 '10 at 09:59
scope_identity will return the last successfully inserted id within the current scope, whether that was the last attempt or not, whether it was the same TABLE or not. output inserted.ID into @tableId is much more reliable at getting the id if the table doesn't have a trigger. – jmoreno Dec 18 '18 at 18:16

score 14 · Answer 4 · edited Dec 02 '18 at 11:46

14

You can try:

SELECT id FROM your_table WHERE id = (SELECT MAX(id) FROM your_table)

Where id is a primary key of the your_table

edited Dec 02 '18 at 11:46

Prashant Pimpale

10,349
9
44
84

answered Jan 29 '15 at 06:17

Hara Das

149
1
5

4

Welcome to Stack Overflow! This Question already has an accepted answer and in Addition ist 4 Years old. In this case there is no additional answer needend. – j_s_stack Jan 29 '15 at 06:39
2

@j_s_stack , he gave a better answer , so why you opine that? – partho Oct 19 '16 at 07:50
1

This is just a copy paste answer. Please remove it. – Reaz Murshed Jul 27 '17 at 07:24
Not sure why this answer posted 5 years after a nearly identical answer below has more upvotes, but I have to agree with j_s_stack and @reaz-murshed. – DavidScherer Apr 28 '20 at 15:38

Pelingier · Answer 5 · 2014-01-20T14:29:27.583

5

I think this one will also work:

SELECT * FROM ORDER BY id DESC LIMIT 0 , 1

edited Jan 20 '14 at 14:29

answered Jan 20 '14 at 14:06

Pelingier

145
2
15

score 4 · Answer 6 · answered Aug 06 '10 at 08:42

4

One more way -

select * from <table> where  id=(select max(id) from <table>)

Also you can check on this link -

http://msdn.microsoft.com/en-us/library/ms175098.aspx

answered Aug 06 '10 at 08:42

Sachin Shanbhag

54,530
11
89
103

that's one more way but I am not sure if it doesn't suffer from the same possible issues that SELECT TOP..does. Would be nice if someone with deeper SQL knowledge comments on both solutions how reliable they are in a high volume usage scenarios. – mare Aug 06 '10 at 08:45

score 2 · Answer 7 · answered Aug 06 '10 at 08:45

2

And if you mean select the ID of the last record inserted, its

SELECT @@IDENTITY FROM table

answered Aug 06 '10 at 08:45

Iain Ward

9,850
5
34
41

score 0 · Answer 8 · answered Oct 13 '20 at 07:28

0

SELECT LAST(row_name) FROM table_name

answered Oct 13 '20 at 07:28

Abduqodir Abdumalikov

11
3

This question already contains multiple answers and an accepted answer. Can you explain (by editing your answer) where your answer differs from the other answers? Also know that Code-only answers are not useful in the long run. – 7uc1f3r Oct 13 '20 at 12:10

Safest way to get last record ID from a table

8 Answers8

Linked