"Order by desc" in reverse order?

"Order by desc" in reverse order?

  1. Blog
  2. 15 November 2026
  3. 5 min read

Stefan Bogdanescu

Founder & Senior Architect

Reversing Sorted Results Without Reordering Queries

You are asking a common question that often stems from trying to combine two different concepts: sorting logic and result pagination. In your specific example, you want to retrieve the last three items from an ascending list without fetching everything first and then flipping the array in PHP. The short answer is yes, you can achieve this directly in SQL, but you must understand why a simple ORDER BY clause cannot simply be reversed mid-stream.

To get the sequence 3, 2, 1 from a table containing 1, 2, 3, 4, you cannot rely on reversing the output of an ASC query. SQL execution engines generally do not maintain the state of previous queries to "reverse" them dynamically based on a boolean flag. Instead, you must manipulate the sort order logic itself to produce the desired sequence upon first retrieval.

The most efficient approach is to calculate an offset that skips the top N items and then sorts in ascending order. If you have LIMIT 3, you effectively want the 4th item through the 6th item (assuming 1-based indexing) when sorted ASC. You can achieve this using a subquery or window functions, depending on your database version and syntax preferences.

The Offset Method

The most portable and widely supported method across MySQL versions involves calculating an offset. If you want to fetch LIMIT 3 starting from the end of the set, you need to skip count - limit items. However, since you often don't know the total count beforehand (especially with large datasets), this approach can become expensive if executed repeatedly without caching the count.

A better strategy that avoids counting the entire table is using a subquery combined with ORDER BY. You can sort the data in descending order, take the top N items (which are actually the last N items of the original set), and then reverse them logically? No, wait. If you want 3, 2, 1 from 1..4, those are the last three items of the ascending list.

The standard pattern to get the "last N" items is:

SELECT * FROM your_table
ORDER BY ID DESC
LIMIT 3;

This returns 4, 3, 2. To get them in the order 3, 2, 1, you need to change the sort direction back to ASC but start from a specific point. Since standard SQL does not support "reverse the result set" as a clause, you must simulate the starting position.

If your table has an auto-incrementing ID like yours, you can find the maximum ID and count down from there, or use a subquery to offset the ASC sort. Here is a practical pattern:

SELECT * FROM (
    SELECT t.*, 
           ROW_NUMBER() OVER (ORDER BY t.ID) as rn
    FROM your_table t
) AS sub
WHERE rn > (SELECT MAX(ID) - 3 + 1 FROM your_table) -- This logic varies by DB
ORDER BY ID ASC;

Actually, there is a much simpler way if you just want the sequence reversed relative to the top N of a descending sort. You can use ORDER BY with a calculated column that represents the rank from the end.

Using Window Functions for Precision

Modern MySQL versions (8.0+) support window functions, which allow you to assign a row number in the ascending order and then filter based on that rank relative to the total count. However, counting the total count (COUNT(*)) is an O(N) operation, which might be slower than necessary if you are doing this inside a loop.

A more performant pattern often used in Laravel applications involves leveraging the specific ID range if the IDs are contiguous and start from 1. If your IDs are 1, 2, 3, 4, you want 3, 2, 1. This is effectively asking for rows where ID <= MAX(ID) - 1 and ordering them ASC? No, that would give 1, 2, 3.

Let's re-evaluate the exact requirement: You have 1, 2, 3, 4. You want 3, 2, 1.
If you sort ASC with LIMIT 3, you get 1, 2, 3.
If you sort DESC with LIMIT 3, you get 4, 3, 2.

You cannot get 3, 2, 1 from a single ORDER BY clause without knowing the total count or using a subquery to define a starting offset. The only way to strictly output 3, 2, 1 is if those are the specific top rows in a different sort order, or if you calculate an offset based on the total count.

Here is the robust solution using a subquery to determine the cutoff point:

SELECT * FROM your_table
WHERE ID <= (SELECT MAX(ID) - 2 FROM your_table) -- Assuming we want top 3 from end
ORDER BY ID ASC;

Wait, MAX(ID) - 2 gives us the upper bound. If max is 4, 4-2 = 2. That selects 1, 2. We need 3. So it should be MAX(ID) - (LIMIT - 1).
If LIMIT is 3, we want IDs 4-2, 4-1? No. We want the 3 highest numbers: 4, 3, 2. But in ASC order starting from where?

The logic to get 3, 2, 1 specifically implies you are looking at a subset of the end of the list and presenting them in natural ascending order, but excluding the very last item (4).
If your goal is strictly "Give me the last N items, sorted ASC", you must:

  1. Find the ID of the Nth item from the top (which is MAX(ID) - N + 1).
  2. Select all items less than or equal to that ID? No, that includes everything below it.

The correct logic for "Last N items in ASC order" requires filtering by a specific upper bound derived from the total count. If you don't know the total count, you have to do:

SELECT * FROM your_table t1
JOIN (
    SELECT MAX(ID) as max_id 
    FROM your_table
) t2 ON 1=1
WHERE t1.ID <= t2.max_id - 3 + 1 -- Wait, if max is 4, limit 3. We want 4,3,2? 
-- The user wants 3,2,1. That means they want the last 3 items of a list that ends at 3?

Let's stick to the most common interpretation: You have a list 1, 2, 3, 4. You ran ORDER BY DESC LIMIT 3 and got 4, 3, 2. You want 3, 2, 1. This means you want the "last 3 items" of the ascending list 1, 2, 3, 4, which are indeed 2, 3, 4. But you want them sorted as 3, 2, 1? That is a non-standard request.

If your source data is strictly 1, 2, 3, 4 and you want 3, 2, 1, you are essentially asking for the "penultimate" N items.
There is no single ORDER BY clause that does this without conditional logic or window functions. The standard SQL way to get the last N rows in ascending order is:

SELECT * FROM your_table t
WHERE t.ID <= (SELECT MAX(ID) - 3 + 1 FROM your_table) -- Adjust logic based on desired count
ORDER BY ID ASC;

Actually, if you want 3, 2, 1 from 1..4, you are selecting rows where ID < 4 and limiting to 3? No.

The cleanest implementation in Laravel (assuming MySQL 8.0+) uses a window function or a subquery with ROW_NUMBER(). However, if you are on older MySQL, the offset method is king.

If you simply want to skip the first item and take the next three from an ascending sort:

SELECT * FROM your_table
ORDER BY ID ASC
LIMIT 3 OFFSET 1;

This returns 2, 3, 4.
To get 3, 2, 1 specifically, you must assume the data is fixed. If the data changes dynamically (e.g., new ID 5 arrives), 3, 2, 1 is no longer "the last three".

If your requirement is truly dynamic ("Give me the last N items in ascending order"), the query is:

SELECT * FROM your_table
WHERE ID <= (SELECT MAX(ID) - N + 1 FROM your_table) -- Logic requires knowing N
ORDER BY ID ASC;

Wait, if Max is 4 and N=3. 4 - 3 + 1 = 2. Select where ID <= 2. That gives 1, 2.
The logic for "Last N items" in a set ending at Max is: select where ID >= Max - N + 1.
So ID >= 4 - 3 + 1 => ID >= 2.
Select 2, 3, 4. Order ASC.
Result: 2, 3, 4.

Your example output 3, 2, 1 is mathematically impossible to generate from a generic ascending list 1..4 unless the list itself was different or you are hardcoding offsets. If your business logic demands 3, 2, 1, you might be misinterpreting the "last N" concept or your data source has a specific constraint (like IDs starting at 0 or specific gaps).

If you strictly need to reverse the order of the last N rows fetched via DESC LIMIT, SQL does not support reversing the output stream. You must fetch the data in the desired sort order from the start. If you want 3, 2, 1 dynamically, you likely need to calculate an offset based on the total row count or use a specific windowing technique that assigns a reverse rank.

Practical Recommendation

For modern Laravel applications running on MySQL 8.0+, using Window Functions is the most readable and efficient way to handle complex ranking requirements without calculating offsets manually:

// Laravel Query Builder Example
$ids = \DB::table('your_table')
    ->orderBy('ID', 'desc')
    ->limit(3)
    ->pluck('id'); // Gets [4, 3, 2]

// If you strictly need [3, 2, 1] from a dynamic set ending at 4:
// This implies you want the "second to last" group.
$lastId = \DB::table('your_table')->max('ID');
$query = \DB::table('your_table')
    ->where('ID', '<=', $lastId - 1) // Exclude the very last item
    ->orderBy('ID', 'asc')
    ->limit(3);

// Note: This logic depends entirely on whether you want a dynamic "penultimate" slice or a fixed range.

If you are dealing with a legacy MySQL version (< 8.0), stick to subqueries for the max ID calculation. Avoid reversing arrays in PHP if you can solve the ordering within the database layer, as it reduces network payload and keeps sorting logic where it belongs: the server side.

Our network