retrieving rows in reverse direction from table using sql limit

Question

I am implementing pagination using SQL limit in MySQL db.Records are needed to get

retrieved from the last row backwards. There are several thousands records in table

and I want to retrieve say 500 records at a time to show up in a page.

SELECT * FROM tbl_name ORDER BY some_col DESC

will retrieve all records.

But I do not want all records, as they are thousands in number.

If I use

SELECT * FROM tbl_name ORDER BY some_col DESC LIMIT 500

it will return last 500 records in descending order.

but I could not find a way to retrieve next block of 500 in reverse direction, starting from a point where first block have left up.

looks similar to this question: stackoverflow.com/questions/1243952/… — Kevin Burton
– Kevin Burton, Commented Sep 29, 2011 at 10:07

Tom H · Accepted Answer · 2011-09-29 11:51:24Z

3

SELECT
    <column list since I never use *>
FROM
    My_Table
ORDER BY
    some_column DESC
LIMIT 500, 500

EDIT: Regarding your comment to Robert's answer... Performance will degrade as the offset gets larger, but the point where the degradation is noticeable is usually pretty large. From an answer that I gave to a similar question on paging a day or two ago:

I am of the strong opinion that a UI should NEVER allow a user to retrieve a set of records that let them go to (for example) page 5000. That's simply too much data for a human mind to find useful all at once and should require further filtering. Maybe let them see the first 100 pages (or some other number), but otherwise they have to constrain the results better. Just my opinion though.

edited Sep 29, 2011 at 11:51

answered Sep 28, 2011 at 14:25

Tom H

47.6k15 gold badges90 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

a Learner Over a year ago

when we run "select * from table_name where 'some condition'" then which of the following statement right? ...all records from 'select' first goes into main memory of our/client computer then 'where' clause is applied there to return selected rows. OR 'where' is directly applied at database server and only rows that satisfy the condition are returned to client

Tom H Over a year ago

The server will only return the rows that are output from the query. The client shouldn't be doing any filtering itself.

Robert Martin · Accepted Answer · 2011-09-28 18:35:44Z

1

The answer is confusing with your hypothetical numbers, so let me make my own:

Let's say we want to display page 3 with 10 records per page. We want to offset by 20 records (the ones on pages 1 & 2), then find 10 records. The format is:

> SELECT * FROM tbl_name ORDER BY some_col DESC LIMIT 20, 10;

In other words, it's

LIMIT <offset>, <max results>

read more: http://php.about.com/od/mysqlcommands/g/Limit_sql.htm

answered Sep 28, 2011 at 18:35

Robert Martin

17.3k17 gold badges65 silver badges89 bronze badges

2 Comments

a Learner Over a year ago

i have several thousands of records and i have read somewhere that when we run a query like "select * from table_name limit 1000000, 100", in which 'offset' is very large, is inefficient to use since this will still traverse through first 1000000 rows to return the next 100 ones.so i want to avoid the offset while using 'limit' clause.

Robert Martin Over a year ago

@brightness This is not a problem as long as you have an index on some_col. With an index, the columns are "sorted" in SQL's mind. Thus, returning rows 1-100 or 1000001-1000100 is the same difficulty. It will simply invoke a binary search through the index, which will take log(N) time.

Jepoy · Accepted Answer · 2025-07-16 03:56:27Z

I may be late for this one but based on my understanding, you wanted to retrieve the data from last row up to the beginning with pagination.

So for example, there are 13 rows of data from the DB and let's say your $per_page = 3, your 1st page would get the data from the 13th down to the 10th row.

If my understanding was correct, then this might be the solution for those are still trying to figure out:

$page     = 1; // ?page=1
$per_page = 3;

$main_sql = "your initial query here"; // without the LIMIT clause and make sure you retrieve the last row first by using ORDER BY clause

$total_rows = "SELECT COUNT(1) FROM ({$main_sql}) AS combined_table"; // get the total rows from the initial query

// set condition to avoid errors
if($page > 0 && $total_rows > 0) {
    $rem = ($total_rows % $per_page); // get the remainder
    $max_pages = ceil($total_rows / $per_page); // get the max pages
    $offset = 0;

    if($total_rows >= $per_page) { // dynamically set the offset value
        $offset = ($total_rows - ($per_page * $page)); 
    }

    if($max_pages == $page) { // reset the offset and per_page if page = max_pages
        $per_page = $rem;
        $offset = 0;
    }
    
    // concatenate the LIMIT clause
    $main_sql .= " LIMIT {$offset}, {$per_page}";
}

Collectives™ on Stack Overflow

retrieving rows in reverse direction from table using sql limit

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related