SQL - How to find the highest number in a column?

Question

Lets say I have the following data in the Customers table: (nothing more)

ID   FirstName   LastName
-------------------------------
20   John        Mackenzie
21   Ted         Green
22   Marcy       Nate

What sort of SELECT statement can get me the number 22, in the ID column?

I need to do something like this to generate a unique ID. Sure I can let the system do this via auto-increment, but then how would I get the auto generated ID?

I thought of "SELECT ID FROM Customers" and counting the rows returned but this seems horribly ineffecient and in this case, it will incorrectly return "3", though I need a unique ID of 23.

Answer 1

You can do

SELECT MAX(ID) FROM Customers;

Answer 2

select max(id) from customers

Answer 3

select max(id) from Customers

Answer 4

To get it at any time, you can do SELECT MAX(Id) FROM Customers .

In the procedure you add it in, however, you can also make use of SCOPE_IDENTITY -- to get the id last added by that procedure. This is safer, because it will guarantee you get your Id--just in case others are being added to the database at the same time.

Answer 5

If you've just inserted a record into the Customers table and you need the value of the recently populated ID field, you can use the SCOPE_IDENTITY function. This is only useful when the INSERT has occurred within the same scope as the call to SCOPE_IDENTITY.

INSERT INTO Customers(ID, FirstName, LastName)
Values
(23, 'Bob', 'Smith')

SET @mostRecentId = SCOPE_IDENTITY()

This may or may not be useful for you, but it's a good technique to be aware of. It will also work with auto-generated columns.

Answer 6

If using AUTOINCREMENT use:

SELECT LAST_INSERT_ID();

Assumming that you are using Mysql: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

Postgres handles this similarly via the currval(sequence_name) function.

Note that using MAX(ID) is not safe, unless you lock the table, since it's possible (in a simplified case) to have another insert that occurs before you call MAX(ID) and you lose the id of the first insert. The functions above are session based so if another session inserts you still get the ID that you inserted.

Answer 7

Depends on what SQL implementation you are using. Both MySQL and SQLite, for example, have ways to get last insert id. In fact, if you're using PHP, there's even a nifty function for exactly that mysql_insert_id().

You should probably look to use this MySQL feature instead of looking at all the rows just to get the biggest insert ID. If your table gets big, that could become very inefficient.

Answer 8

If you're not using auto-incrementing fields, you can achieve a similar result with something like the following:

insert into Customers (ID, FirstName, LastName)
    select max(ID)+1, 'Barack', 'Obama' from Customers;

This will ensure there's no chance of a race condition which could be caused by someone else inserting into the table between your extraction of the maximum ID and your insertion of the new record.

This is using standard SQL, there are no doubt better ways to achieve it with specific DBMS' but they're not necessarily portable (something we take very seriously in our shop).

Answer 9

If you're talking MS SQL, here's the most efficient way. This retrieves the current identity seed from a table based on whatever column is the identity.

select IDENT_CURRENT('TableName') as LastIdentity

Using MAX(id) is more generic, but for example I have an table with 400 million rows that takes 2 minutes to get the MAX(id). IDENT_CURRENT is nearly instantaneous...