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

Question

Let's 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 inefficient, 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

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.