0

I can use the offset and limit function but how do I fetch the next rows starting from the offset of the last fetched row.

Suppose I have a dataset of 50000 items and want to display 50 items on page 1 so I want to fetch only 50 items and when I click on next page it should fetch and display the next 50 items.

Kris Rice
  • 2,995
  • 11
  • 31
captain n3mo
  • 5
  • 3
  • This is more of an application development question is my opinion. The answer is that you need to maintain some state as to which page your user is on currently. – Tim Biegeleisen Jun 08 '18 at 05:16
  • how do I maintain that state. I am a bit new to sql – captain n3mo Jun 08 '18 at 05:20
  • Say you have your application that presents data to user; user switches from page to page; in every time you need to know the page you want to show, so that you can make a query like "select the 3rd set of 50 records". Once you have that information, you may search for "pagination" or similar and you'll find many good answers about how to do it in Oracle – Aleksej Jun 08 '18 at 05:24
  • okay. pagination seems to be the thing I am looking for. thank you – captain n3mo Jun 08 '18 at 05:25
  • 1
    Possible duplicate of [How do I limit the number of rows returned by an Oracle query after ordering?](https://stackoverflow.com/questions/470542/how-do-i-limit-the-number-of-rows-returned-by-an-oracle-query-after-ordering) – Aleksej Jun 08 '18 at 05:39
  • Oracle 12c has an offset feature. https://docs.oracle.com/database/121/SQLRF/statements_10002.htm#BABEAACC SELECT employee_id, last_name FROM employees ORDER BY employee_id OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY; – thatjeffsmith Jun 08 '18 at 11:44

1 Answers1

0

If you want to fetch the data from the offset in mysql, you can use the keyword like.

SELECT * FROM 'table_name' LIMIT 51,50

This will fetch item number 51 to next 50 items. I the program you can multiply number (51) to get the further results.

Multiplying syntax depends on which backend are you using suppose you are using, Basically you have to put a loop around it in order to change the offset value. I am writing the PHP code:

for($i=0; $i< $n; $i++){
    $offset=50*$i;
    $sql = "SELECT * FROM 'table_name' LIMIT {$offset} ,50  "
    ...
    // Now fire this sql query.
}

If you want to do this with Django there is pagination

Rajat Jain
  • 1,155
  • 2
  • 12
  • 28