CODEWITHSUNDEEP
sqlsql-server-2008
edgarmtze
edgarmtze

Reputation: 25058

Last id value in a table. SQL Server

Is there a way to know the last nth id field of a table, without scanning it completely? (just go to the end of table and get id value)

table
id   fieldvalue
1    2323
2    4645
3    556
...  ...
100000000  1232

So for example here n = 100000000 100 Million

--------------EDIT----- So which one of the queries proposed would be more efficient?

Upvotes: 5

Views: 47217

Answers (6)

Md Shahriar
Md Shahriar

Reputation: 2776

Select Ident_Current('Your Table Name') gives the last Id of your table.

Upvotes: 0

rsbarro
rsbarro

Reputation: 27349

Assuming ID is the IDENTITY for the table, you could use SELECT IDENT_CURRENT('TABLE NAME').

See here for more info.

One thing to note about this approach: If you have INSERTs that fail but increment the IDENTITY counter, then you will get back a result that is higher than the result returned by SELECT MAX(id) FROM <tablename>

Upvotes: 6

vooxo
vooxo

Reputation: 31

In case when table1 rows are inserted first, and then rows to table2 which depend on ids from the table1, you can use SELECT:

INSERT INTO `table2` (`some_id`, `some_value`)
VALUES ((SELECT some_id
        FROM `table1`
        WHERE `other_key_1` = 'xxx'
            AND `other_key_2` = 'yyy'),
        'some value abc abc 123 123 ...');

Of course, this can work only if there are other identifiers that can uniquely identify rows from table1

Upvotes: 1

kosmo
kosmo

Reputation: 141

You can also use system tables to get all last values from all identity columns in system:

select
    OBJECT_NAME(object_id) + '.' + name as col_name
    , last_value 
from 
    sys.identity_columns 
order by last_value desc

Upvotes: 2

Michael Dillon
Michael Dillon

Reputation: 32392

First of all, you want to access the table in DESCENDING order by ID.

Then you would select the TOP N records.

At this point, you want the last record of the set which hopefully is obvious. Assuming that the id field is indexed, this would at most retrieve the last N records of the table and most likely would end up being optimized into a single record fetch.

Upvotes: 0

rayman86
rayman86

Reputation: 1395

SELECT MAX(id) FROM <tablename>

Upvotes: 24

Related Questions