How to Get the ID of the Latest Inserted Record in MySQL

1. Get the ID of the Latest Inserted Record in MySQL Table
2. Use the LAST_INSERT_ID() Function to Get the ID of the Last Inserted Row in MySQL
3. Use the MAX() Function to Get the ID of the Last Inserted Row in MySQL
4. Use the ORDER BY DESC to Get the ID of the Last Inserted Row in MySQL

This tutorial explains three approaches named LAST_INSERT_ID(), MAX(), and ORDER BY DESC with code examples and demonstrates how to get the ID of the latest inserted record in MySQL.

Get the ID of the Latest Inserted Record in MySQL Table

There are 3 ways that we can use to get an ID of the latest inserted record in the MySQL table. All of these are listed below, and the table must have an AUTO_INCREMENT field to use any of the following approaches.

1. Use the LAST_INSERT_ID() function.
2. Use the max() function.
3. Use the ORDER BY DESC clause.

Use the LAST_INSERT_ID() Function to Get the ID of the Last Inserted Row in MySQL

The LAST INSERT ID() function can be used to get the ID of the last inserted or updated record (row) when using the INSERT or UPDATE command on a table with the AUTO INCREMENT field.

By preparing the tb_courses table and populating it with sample data, let’s understand it.

Example Query:

# create a table
CREATE TABLE tb_courses (
      ID INT NOT NULL AUTO_INCREMENT,
      COURSE_NAME VARCHAR(255) NOT NULL,
      PRIMARY KEY (id)
);

# insert data
INSERT INTO tb_courses (COURSE_NAME) VALUES ('Introduction to Java');
INSERT INTO tb_courses (COURSE_NAME) VALUES ('Python for Beginners');
INSERT INTO tb_courses (COURSE_NAME) VALUES('Database Systems');

# display data
SELECT * FROM tb_courses;

Output:

+----+----------------------+
| ID | COURSE_NAME          |
+----+----------------------+
|  1 | Introduction to Java |
|  2 | Python for Beginners |
|  3 | Database Systems     |
+----+----------------------+
3 rows in set (0.00 sec)

Now, execute the following command to retrieve the ID of the latest inserted record.

Example Query:

SELECT LAST_INSERT_ID();

Output:

+------------------+
| LAST_INSERT_ID() |
+------------------+
|                3 |
+------------------+
1 row in set (0.00 sec)

It returns the correct value, 3 is the last inserted ID. Now, we INSERT some more data and insert multiple rows.

Example Query:

# insert data
INSERT INTO tb_courses (COURSE_NAME)
VALUES
('Introduction to Machine Learning'),
('Deep Learning'),
('Statistics');

# display data
SELECT * FROM tb_courses;

Output:

+----+----------------------------------+
| ID | COURSE_NAME                      |
+----+----------------------------------+
|  1 | Introduction to Java             |
|  2 | Python for Beginners             |
|  3 | Database Systems                 |
|  4 | Introduction to Machine Learning | <======================
|  5 | Deep Learning                    |
|  6 | Statistics                       |
+----+----------------------------------+
6 rows in set (0.00 sec)

Run the following query to get the latest inserted ID.

Example Query:

SELECT LAST_INSERT_ID();

Output:

+------------------+
| LAST_INSERT_ID() |
+------------------+
|                4 |
+------------------+
1 row in set (0.00 sec)

We can see the output where the LAST_INSERT_ID() method returns a generated value of the first record, not the last record. We only get the last inserted ID using the LAST_INSERT_ID() if we use a single INSERT statement for one row.

If we use a single INSERT statement for more than one row, the LAST_INSERT_ID() function will output the generated value for the first inserted record only (see the output given above). Remember, the generated ID is maintained on a per-connection basis in a server.

The first AUTO INCREMENT value generated for the most recent statement influencing the AUTO INCREMENT field by that client is returned by the LAST INSERT ID() method to that client. So, the returned value of the LAST_INSERT_ID() is per user, not affected by other users’ other statements executed on the server.

Use the MAX() Function to Get the ID of the Last Inserted Row in MySQL

We can use the MAX() function to get the row’s last inserted ID. It does not matter if we use a single INSERT statement for one or multiple rows for this method.

First, we use the MAX() function to populate the tb_courses table using one row’s single INSERT statement.

Example Query:

INSERT INTO tb_courses (COURSE_NAME) VALUES ('Data Science');
SELECT MAX( ID ) FROM tb_courses;

Output:

+-----------+
| MAX( ID ) |
+-----------+
|         7 |
+-----------+
1 row in set (0.02 sec)

It returned the value 7, which is correct. We use a single INSERT statement for multiple rows and then the MAX() function to retrieve the last inserted ID.

Example Query:

INSERT INTO tb_courses (COURSE_NAME)
VALUES
('Introduction to Recommender Systems'),
('Data Structures'),
('Analysis of Algorithms');

SELECT MAX( ID ) FROM tb_courses;

Output:

+-----------+
| MAX( ID ) |
+-----------+
|        10 |
+-----------+
1 row in set (0.00 sec)

It returns the correct value, which is 10.

Use the ORDER BY DESC to Get the ID of the Last Inserted Row in MySQL

The ORDER BY DESC clause can also be used to sort data in descending order using the ID field and getting the ID from the first row (which was the last row before sorting).

Example Query:

SELECT ID FROM tb_courses ORDER BY ID DESC LIMIT 1;

Output:

+----+
| ID |
+----+
| 10 |
+----+
1 row in set (0.05 sec)