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

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.

select max(id) from customers

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...

You can also use relational algebra. A bit lengthy procedure, but here it is just to understand how MAX() works:

E := p_ID (Table_Name)
E1 := p_ID (s_{ID >= ID'} ((?_ID' E) ? E)) – p_ID (s_{ID < ID’} ((?_ID' E) ? E))

Your answer: Table_Name ? E1

Basically what you do is subtract set of ordered relation(a,b) in which a<b from A where a, b ? A.

For relation algebra symbols see: Relational algebra From Wikipedia

To find the next (still not used) auto-increment, I am using this function for somewhat years now.

public function getNewAI($table)
    {
        $newAI = false;

        $mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
        if(mysqli_connect_errno()) {
            echo "Failed to connect to MySQL: " . mysqli_connect_error();
        }

        $sql = "SHOW TABLE STATUS LIKE '".$table."'";

        $result = $mysqli->query($sql);

        if($result) {
            $row = $result->fetch_assoc();

            $newAI = $row['Auto_increment'];
        }
        $mysqli->close();

        return $newAI;
    }

select * from tablename order by ID DESC

that will give you row with id 22

If you are 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.

In PHP:

$sql = mysql_query("select id from customers order by id desc");
$rs = mysql_fetch_array($sql);
if ( !$rs ) { $newid = 1; } else { $newid = $rs[newid]+1; }

thus $newid = 23 if last record in column id was 22.

select max(id) from Customers 

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.

If you want to just select the id use select max(id) from customer.

If you want to select the entire row then use a query like this:

select c1.*
from customer c1, (select max(id) as max_id from customer )c2 
where c1.id=c2.max_id

c2 is an alias for the new temporary table which contains max id. Then its cross product is taken with customer table to get the entire row.

Here we are writing a query in the from clause, which can often be quite useful.

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.

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).

Here's how I would make the next ID:

INSERT INTO table_name (
            ID,
            FIRSTNAME,
            SURNAME) 
            VALUES ((( 
                    SELECT COALESCE(MAX(B.ID)+1,1) AS NEXTID 
                        FROM table_name B
                        )), John2, Smith2);

With this you can make sure that even if the table ID is NULL, it will still work perfectly.

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.

SELECT * FROM Customers ORDER BY ID DESC LIMIT 1

Then get the ID.