Introduction

To select the "last N rows" in MySQL, you first need a column that defines what "last" means. SQL tables are unordered sets by default, so the normal solution is to sort by an ID or timestamp in descending order and then use LIMIT.

The Basic Pattern

If id is an increasing primary key, the standard query is:

1SELECT *
2FROM orders
3ORDER BY id DESC
4LIMIT 5;

This returns the five rows with the largest IDs. In many applications, that is what people mean by "last 5 rows".

The same pattern works with timestamps:

1SELECT *
2FROM orders
3ORDER BY created_at DESC
4LIMIT 5;

That is often more correct than relying on id if business recency matters.

Return the Results in Natural Ascending Order

A common follow-up requirement is:

• find the latest 5 rows
• but display them oldest-to-newest within that subset

Use a subquery:

1SELECT *
2FROM (
3    SELECT *
4    FROM orders
5    ORDER BY id DESC
6    LIMIT 5
7) AS last_rows
8ORDER BY id ASC;

The inner query grabs the newest five rows. The outer query reorders just those five for presentation.

Why LIMIT Alone Is Not Enough

This is wrong if you mean "last rows":

SELECT *
FROM orders
LIMIT 5;

Without ORDER BY, MySQL is free to return rows in whatever order the execution plan produces. That may look stable in testing and then change later after an index change, engine choice, or optimizer decision.

So the rule is simple: if you care about "last", always define an order explicitly.

Choose the Right Ordering Column

People often default to id, but that is only a proxy for recency. In some systems:

• IDs are imported in batches
• IDs come from multiple writers
• older records are inserted late

In those cases, the "largest ID" may not equal the "most recent event". If the business concept is recency, use a timestamp column such as created_at.

Indexing Matters

If the query runs often, index the ordering column. For example:

CREATE INDEX idx_orders_created_at ON orders(created_at);

That helps MySQL find the top rows efficiently, especially when the table is large. The same idea applies to an indexed primary key, which is why ordering by id is often cheap.

Filtering and Then Taking the Last N

You can combine filters with the same pattern:

1SELECT *
2FROM orders
3WHERE status = 'paid'
4ORDER BY created_at DESC
5LIMIT 10;

This means "the last 10 paid orders", not "the last 10 orders and then filter those for paid". The distinction is important and sometimes changes the result significantly.

Common Pitfalls

• Using LIMIT without ORDER BY and assuming the rows are naturally in insertion order.
• Ordering by id when the actual requirement is based on a timestamp or business event time.
• Forgetting that ORDER BY ... DESC LIMIT N returns newest-first, which may not be the display order you want.
• Running the query on a large table without an index on the ordering column.
• Writing a filter after grabbing the last N rows in a subquery when the intended logic was to filter first.

Summary

• In MySQL, "last N rows" means ordering by a meaningful column and using LIMIT.
• The standard form is ORDER BY ... DESC LIMIT N.
• If you want the selected subset displayed oldest-to-newest, wrap it in a subquery and reorder outside.
• Use a timestamp when recency matters more than raw ID size.
• Without ORDER BY, there is no guaranteed meaning to "last".