How to select the last record from a MySQL table using SQL syntax

sql

join

best-practices

performance

byAlex Kataev·Dec 2, 2024

To fetch the latest record in a table, use the following SQL command:

SELECT * FROM table_name
ORDER BY id DESC
LIMIT 1;

Here, ensure to replace table_name with your specific table, and id with the column that uniquely identifies each record. Typically, id is an auto-incrementing primary key. This query sorts records from most recent to oldest and finds only the most recent one.

Unpacking the query

Understanding "ORDER BY" and "LIMIT"

ORDER BY is that lifesaver clause that orders rows in the result set, and with the DESC keyword, it puts the row with the highest ID on top — just like at the royal court.

As for the LIMIT clause, it's like your overprotective parent, limiting the rows that come to you to just one, ensuring you only get the newest record.

The "MAX()" function and its use

If you only care about knowing the highest id and not the whole record — MAX() function is your best buddy:

SELECT MAX(id) FROM table_name; -- King Maximus, find the highest ID!

To get the entire last record using MAX(), you would do a subquery dance:

SELECT * FROM table_name WHERE id = (SELECT MAX(id) FROM table_name);

Let's play "This or That"

ORDER BY ... LIMIT sprints faster on tables with a large number of records, if a suitable index exists, it's a Usain Bolt against a snail.

On the other hand, using MAX() is more direct and easier on the eyes when you just want the latest id.

Performance & Indexing

Indexes are key... Literally!

For databases resembling the Library of Alexandria in size, indexes retain performance. A handy id column index, often the primary key, ensures ORDER BY won't slow you down.

Unexpected Hiccups

Assuming id is the highest for the latest record implies a consistent increment. Be mindful of this, records might be deleted or the id is not your timeline pointer, it might end up misleading you.

Other ways to retrieve last record

Let's dance the "LAST_INSERT_ID()" waltz

To know the id of the last inserted row, join hands with LAST_INSERT_ID():

SELECT * FROM table_name WHERE id = LAST_INSERT_ID(); -- Find me that last sneaky insertion!

The catch? This works only if the query is invited to the same database party as the insert operation.

Composite keys & non-integer IDs

You’re not always dealing with a single id— sometimes it's a composite key or a non-integer id. Adjust the ORDER BY clause to mirror the uniqueness of your records.

"JOIN"ing the big leagues

When the "last record" assumes more complexity than the highest id, you might need a more intricate query involving JOIN and grouping — They may be overwhelming at first but are quite the party animals:

SELECT t1.*
FROM table_name AS t1
JOIN (
    SELECT category, MAX(datetime_field) AS max_datetime
    FROM table_name
    GROUP BY category
) AS t2 ON t1.category = t2.category AND t1.datetime_field = t2.max_datetime; -- Yes, it's complicated!