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

Jeremy_Rudd_41021 0
Viewed 157624

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.

sql-server mysql sql
16 Answers

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.

user_149938930

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.

user_100437680

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;
    }
user_41132460
Related