How to query first 10 rows and next time query other 10 rows from table

Question

I have more than 500 rows with in my Database Table with particular date    To query the rows with particular date   select   from msgtable where cdate  18 07 2012    This returns 500 rows   How to query these 500 rows by 10 rows step by step   Query First 10 Rows and show in browser then query next 10 rows and show in browser

User · Answer

LIMIT limit OFFSET offset will work   But you need a stable ORDER BY clause  or the values may be ordered differently for the next call  after any write on the table for instance    SELECT   FROM   msgtable WHERE  cdate    2012-07-18  ORDER  BY msgtable id  -- or whatever is stable  LIMIT  10 OFFSET 50   -- to skip to page 6  Use standard-conforming date style  ISO 8601 in my example   which works irregardless of your locale settings   Paging will still shift if involved rows are inserted or deleted or changed in relevant columns  It has to   To avoid that shift or for better performance with big tables use smarter paging strategies    Optimize query with OFFSET on large table

User · Answer

Just use the LIMIT clause   SELECT   FROM  msgtable  WHERE  cdate   18 07 2012  LIMIT 10   And from the next call you can do this way   SELECT   FROM  msgtable  WHERE  cdate   18 07 2012  LIMIT 10 OFFSET 10   More information on OFFSET and LIMIT on LIMIT and OFFSET

User · Answer

SET  rownum   0   SELECT sub    sub rank as Rank FROM      SELECT       rownum     rownum   1  as rank    FROM msgtable     WHERE cdate    18 07 2012    sub WHERE rank BETWEEN    PageNum - 1     PageSize   1    AND   PageNum    PageSize    Every time you pass the parameters  PageNum and the  PageSize to get the specific page you want  For exmple the first 10 rows would be  PageNum   1 and  PageSize   10

User · Answer

Ok  So I think you just need to implement Pagination    perPage   10    pageNo     GET  page      Now find total rows in database    totalRows   Get By applying sql query    pages   ceil  totalRows  perPage         offset     pageNo - 1     perPage   1   sql    SELECT   FROM msgtable WHERE cdate  18 07 2012  LIMIT    offset       perPage

User · Answer

for first 10 rows     SELECT   FROM msgtable WHERE cdate  18 07 2012  LIMIT 0 10   for next 10 rows  SELECT   FROM msgtable WHERE cdate  18 07 2012  LIMIT 10 10

User · Answer

You can use postgresql Cursors  BEGIN  DECLARE C CURSOR FOR where   FROM msgtable where cdate  18 07 2012     Then use  FETCH 10 FROM C    to fetch 10 rows   Finnish with  COMMIT    to close the cursor   But if you need to make a query in different processes  LIMIT and OFFSET as suggested by  Praveen Kumar is better

User · Answer

lt html gt   lt head gt       lt title gt Pagination lt  title gt   lt  head gt   lt body gt   lt  php        conn   mysqli connect  localhost   root      northwind         data per page   10       select    SELECT   FROM  customers           select run   mysqli query  conn   select        records   mysqli num rows  select run          while   result   mysqli fetch array  select run                      echo  result  CompanyName       lt br gt                             records      echo   lt br gt         no of page   ceil  records    data per page       if  isset   GET  page               page   1       else           page     GET  page               page limit data     page - 1    10       select    SELECT   FROM customers LIMIT      page limit data          data per page        select run   mysqli query  conn   select       while   row select   mysqli fetch array  select run            echo  row select  CompanyName       lt br gt               for  page 1   page lt    no of page   page              echo   lt a href  pagination php page  page  gt   page                     gt   lt br gt   lt h1 gt  Testing Limit Functions Here   lt  h1 gt   lt  php   limit    SELECT CompanyName From customers LIMIT 10 OFFSET 5    limit run   mysqli query  conn    limit   while  limit result   mysqli fetch array  limit run        echo  limit result  CompanyName       lt br gt        gt   lt  body gt   lt  html gt

[sql] How to query first 10 rows and next time query other 10 rows from table

Examples related to sql

Examples related to postgresql