I'm afraid you need to fetch the preceding records... i know no other way to make the db-cursor moving forward or skipping directly the record no ##.
Best regards,

King Louie

    I authored the stored procedure sp_get_posts and it works really, really fast.

    Another method, if you don't want to use a stored procedure, is to do something like:
    SELECT LIMIT start_at, number

    SELECT TOP number
  FROM table
 WHERE id NOT IN ( SELECT TOP start_at
                     FROM table
                    ORDER BY id ASC )
 ORDER BY id ASC

    There are other ways, experiment with TOP n if you want.

      2 years later

      MATTR,

      I was wondering... Can you copy and paste that stored proceedure? If you still have it? or do you have a function for this paging (MSSQL)??

      Seems that SO many people are looking for this paging with MSSQL and MS is doing nothing about it.

      I could really use seeing an example...

      Thanks much for your time!!!

        Curious though.. this seems to be something that a lot of people are looking for.. why isnt there solutions posted throughout the web? This is not the first post I have made about this.. but seems many choose to ignore this issue.. 🙁

        Please help

          I wrote this long ago (rather wishing that MsSQL implemented LIMIT.) This was my fix. Seems like it was based off an example on the Net somewhere (however, it has been like 3 years! Can't remember how much of it was my ingenuity.) The downside was that it didn't seem to lend itself well to displaying order records (in this case) ordered by time descending. Then again, the presentation tier was done in VBstupid ;-)

          CREATE PROCEDURE sProc_Get_Orders

          (
@limit	[int],
@total	[varchar](50)
)

          AS
          DECLARE
          @SQL nvarchar(1000)
          set @SQL = 'SELECT idx, b_firstname, b_lastname, modified, created,funds_pending FROM orders WHERE idx IN (SELECT TOP ' + @total + ' idx FROM orders WHERE (deleted IS NULL) ORDER BY idx DESC) ORDER BY idx'

          --PRINT @SQL
          SET ROWCOUNT @
          EXEC (@SQL)
          GO

            Man, this is the first time I ever posted ASP on a PHP forum (hope this isn't a party foul!)

            I thought it would be helpful to show you how I implemented this stored procedure. You can translate to PHP (if not I'm sure I can.)

            Dim arrRows, i
            Dim strPage, intPages, strRecTotal, strLimit
            Dim strStatus, strFundsPending

            Response.Expires = 0

            'Return only 10 rows per page
            strLimit = 10

            strFundsPending = "1"
            If Trim(Request.QueryString("type")) <> Empty AND IsNumeric(Request.QueryString("type")) Then
            strFundsPending = Request.QueryString("type")
            End If

            sql = "SELECT COUNT(*) FROM orders WHERE deleted IS NULL"
            ' WHERE funds_pending = " & strFundsPending
            Set rs = conn.execute(sql)
            If not rs.EOF Then
            strRecTotal = rs(0)
            End If
            Set rs = nothing

            'Response.write "<BR>QUERY COUNT:" & strRecTotal

            intPages = strRecTotal\strLimit
            strPage = Server.HTMLEncode(Request.QueryString("pg"))
            If strPage <> Empty Then
            strPage = cInt(strPage)
            strRecTotal = strRecTotal - (strLimit * strPage)
            If strRecTotal < 0 Then strRecTotal = 0
            Else
            strPage = 0
            End If

            sql = "sProc_Get_Orders " & strLimit & ", " & strRecTotal & ", " & strFundsPending

            P.S. Pay no attention to the funds pending parts.

              Stripe-man wrote:

              Curious though.. this seems to be something that a lot of people are looking for.. why isnt there solutions posted throughout the web? This is not the first post I have made about this.. but seems many choose to ignore this issue.. 🙁

              True, I have the same problem with MS Access. Seems nobody really knows how to do this. Bummer ! (:

                True, I have the same problem with MS Access. Seems nobody really knows how to do this. Bummer ! (:

                Yeah, a stored procedure isn't very compatible with Access (then again, you should use SQLite instead.) However, the stored procedure example is a fast solution as ignored records are not returned to the client. I remember I looked and looked, so my response may be the "answer" afterall. This is just a case of providing some extra data-layer logic as I'll assume you can call stored procedures in PHP. I mean you specify a start and length with MySQL LIMIT also.

                  But in looking at everyone’s examples.. I now have an understanding in how this has to work (least without the stored procedure) I will also discuss this stored procedure with our DB Guru...

                  BUT... This is what I have and understand...

                  THIS WORKS!!!!

                  SELECT TOP 10 * FROM main
	WHERE published = 1 and id NOT IN ( SELECT TOP 0 id
                     FROM main  ORDER BY id ASC )
 ORDER BY id ASC

                  Explanation:

                  SELECT TOP 10 * FROM main

                  The first part of this script will specify how many records to return This will always remain the same (course you can offer to the user how many records per page he wants with this.. Just substitute the "10" with a $var.

                  and id NOT IN ( SELECT TOP 0 id

                  This means its making sure that the records fetched in the first select are NOT selected in the second select. so .. with specifying '0' or zero.. this could be your first page. The '0' or zero would have to be replaced with a $var to specify how many records you want to skip.

                  So.. if I change the '0' to 10 for example.. Then the query would look like this..

                  SELECT TOP 10 * FROM main
	WHERE published = 1 and id NOT IN ( SELECT TOP 10 id
                     FROM main  ORDER BY id ASC )
 ORDER BY id ASC

                  This means select the first 10 records that are NOT in the first 10 records so it would then select the next 10 records. If I change it to 20 then it would then skip the first 20 records and so on...
                  IE:

                  SELECT TOP 10 * FROM main
	WHERE published = 1 and id NOT IN ( SELECT TOP 20 id
                     FROM main  ORDER BY id ASC )
 ORDER BY id ASC

                  of course the sorting need to be identical.

                  Example with $var:
                  $records_per_page = 10; //can use $GET from URL
                  $next_page = $next_id; //can use $                  GET from URL

                  SELECT TOP $records_per_page * FROM main
	WHERE published = 1 and id NOT IN ( SELECT TOP $next_page id
                     FROM main  ORDER BY id ASC )
 ORDER BY id ASC

                  Does this help anyone?
                  I may build a function for this...

                    Wow, like I said I implemented my stored procedure approach a few years back. I'm not sure with the NOT IN and the ASC order make the difference (I'll have to read your post again better or digest for myself), however this way works much better as exactly the right ecords are returned.

                    My way simply used SET ROWCOUNT @ to make sure only @ records are returned to the client cursor. It was efficient in that respect but not like this one on the server end of things (and this way is much less confusing to use.)

                    Thanks for some enlightenment mattr and Stripe-man!

                      Thanks Bretticus... I am at work now .. and i hope I have time today to discuss this stored proceedure with our db guru... Thanks much for your time and input on this.. Great stuff.. its nice to know that someone out tehre still is willing to help!

                      and I will play with the sorting.. currently my brain is telling me that I must specify the sort on both queries, mostly because (i think mssql sorts asc by default.. but what if i want to sort DESC ? So i think i need it in there.. the sorting capabilities I am giving my users are very entailed. 10 or 12 columns with each one having the asc and desc capability/ not to mention expanding or filtering the results by, country, date, article groups and other stuff... you should see my query and the php behind it ! LOL

                        Hey, thanks again, I tried ORDER BY idx DESC on my query and sub query. Works great.

                        Here's my current stored procedure...

                        CREATE PROCEDURE sProc_Get_Orders

                        (
@limit	[int],
@total	[varchar](50)
)

                        AS
                        DECLARE
                        @SQL nvarchar(1000)
                        set @SQL = 'SELECT TOP '+CAST ( @ AS varchar(20) ) +' idx, b_firstname, b_lastname, modified, created,funds_pending FROM orders WHERE idx NOT IN (SELECT TOP ' + @total + ' idx FROM orders WHERE (deleted IS NULL) ORDER BY idx DESC) AND (deleted IS NULL) ORDER BY idx DESC'
                        EXEC (@SQL)
                        GO

                          Thanks for posting bretticus... but is this MSsql... or for another db type?

                          I am getting syntax errors while checking the syntax... 🙁

                            wow.. good find Bretticus! LOL .. I will read up on that tomorrow...THans for the link.. I have already consolidated the content (all pages) and converted it to a doc file!

                            I am working on a function for the paging (client side) as well.

                              4 months later

                              Hats-off, Stripe-man, hats-off. A very pretty solution. 🆒

                              This has always been a problem in ms-land, and it's nice to know how to do it should the need arise. I suppose the charitable answer as to why M$ insist on sticking with TOP instead of implementing LIMIT is because we mostly use their products for client/server and so send the whole resultset to the client for pagination there anyway.

                                Raul!!! you found my post!!!! Roger Ramjet I must tell you.. Raul solution is the pick of all time.. I have already begun to intergrate it.. and Raul has already is also currently updating his script to make it even more flexible. Its a true enterprise level class. YOu must see it and try it.