Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Divide a recordset

Posted on 2002-06-11
19
365 Views
Last Modified: 2008-02-01
Say I have a 100 records of whatever. I want to run a SQL statement against it but somehow break up the recordset in groups of, say, 10. Meaning to say, the first set would have records 1-10, the next set 11-20, and so on until 91-100.

I kind of want to mimic what EE does with its questions -- under "Questions waiting to be answered," it only lists the latest 20 unanswered question. You can click the link that says "Next 50" to see the rest of the unanswered questions.
0
Comment
Question by:dbnewbie
  • 9
  • 5
  • 3
  • +1
19 Comments
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7071820
There must be an Identity field on the table.  The Create this proc:

    Create Proc spFetchChunk
    @From Int,
    @To Int
    AS

    Declare @SQL VarChar(200)

    Set @SQL = 'select TOP ' + Convert(Varchar,@To-@From) + ' *
    from
        (select TOP ' + Convert(Varchar,@From) + ' *
        from Table
        order by ID ASC) AS AA
    order by ID DESC'

    --Print @SQL
    Exec (@SQL)
    Go

(Be sure to replace the word table with table name you're actually dealing with) Now to test run this:

exec spFetchChunk 5, 10

You should be abble to call this proc from your front end with something likr ADO passing the start and stop record number as parameters . . .

=)
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7071843
Sorry that first proc doesn;t work . . . use this one instead:

    Create Proc spFetchChunk
    @From Int,
    @To Int
    AS

    Declare @SQL VarChar(200), @Rows Int

    Set @SQL = 'Select * From (select TOP ' + Convert(Varchar,(@To - @From + 1)) + ' *
    from
        (select TOP ' + Convert(Varchar,@To) + ' *
        from TitleAuthor
        order by ID ASC) AS AA
    order by ID DESC) B
    Order By ID ASC'

--    Print @SQL
    Exec (@SQL)
    Go

Have fun =D
0
 

Author Comment

by:dbnewbie
ID: 7072345
oh oh.

i'm not using MS-SQL. i'm actually developing this in ASP. just needed some help w the SQL. will this work?
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 5

Expert Comment

by:spcmnspff
ID: 7072584
No this is strictly an MS-SQL solution,  If you have a writing asp with a MSSQL backend, this will work.  Which DBMS are you using?
0
 

Author Comment

by:dbnewbie
ID: 7072629
isn't SQL supposed to be a standard? anyway, all i'm connecting to is an MS Access database. however, should i choose to upgrade later, i would like my SQL code to still work. is it possible?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7072941
Sure SQL is a "standard", that does not mean that does not prevent every product under the sun, even from the same company, producing it's own extensions, so no, in general they are not totally compatible.  Since this is a MS-SQL topic area the solutions offered will be those that work in a MS SQL Server environment and for Access type questions you would be better off in the MS Access topic area (http://www.experts-exchange.com/msaccess/)

But in any case what you are looking for is something in the lines of the following.  Supposing you had a recordset with 50 rows (using the Customers table in the Northwind database) then the following would give you the first 10 rows:

Select Top 10 * From Customers Order By CustomerID

For the next 10 rows (11 to 20)
Select Top 10 * From  (Select Top 40 * from Customers Order by Customerid Desc) Order By CustomerID

For the next 10 rows (21 to 30)
Select Top 10 * From  (Select Top 30 * from Customers Order by Customerid Desc) Order By CustomerID

For the next 10 rows (31 to 40)
Select Top 10 * From  (Select Top 20 * from Customers Order by Customerid Desc) Order By CustomerID

For the last 10 rows:
Select Top 10 * From  (Select Top 10 * from Customers Order by Customerid Desc) Order By CustomerID

Anthony
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7072984
There are SQL standards, but they aren't always strictly adhered to.
The problem is that the TOP functionality is strictly a T-SQL feature.

Okay this will work in Access with a little modification:
Use this to concatenate your sql.  Then use this as the sql for an ADO or DAO recordset.

 Dim SQL, A, B
 A= 5
 B = 10
 
 SQL ="Select * from Table Where JobID In" & _
    "(Select Top " & B-A+1 &" JobId From Table where JobId In" & _
        "(Select TOP " & B & " JobID from Table order by JobID ASC)" & _
    "Order By JobId DESC)" & _
"Order by JobID ASC"
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7073004
>> The problem is that the TOP functionality is strictly a T-SQL feature.<<
Actually the TOP keyword has been in MS Access a lot longer than MS SQL Server.  It was only introduced to MS SQL Server in version 7.

Anthony
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7073021
Yes, Anthony, but I consider Jet SQL a crippled T-SQL syntax anyway.  With differences like using * instead of % and # to designate dates, etc.  Jet SQL wants to be T-SQL but just doesn't deliver all the functionality.

BTW: Your's doesn't work with Access because JET SQL doesn't support virtual tables. Use a subquery instead . . . =)


0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7073044
>>BTW: Your's doesn't work with Access because JET SQL doesn't support virtual tables. Use a subquery
instead . . . =)<<
That is odd, because I tested it before posting.  Did you?

Anthony
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7073058
Yes: Access 97 office pack 2, would not let me create a query with a vitual table . . . let me try again . . .
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7073064
Yeah, "Syntax error in from clause"  . . . Maybe you have the  MS Jet Update installed . . . I didn't bother with it . . rarely use Access anymore.  =)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7073081
Access 2000.  Same here, I rarely use it.

Anthony
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7073083
Though it is good to know that syntax does not work with older versions of Access.

Anthony
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7073112
Yeah, access is a good tool to get something done quickly,  but it can be quite abusive to your SQL server . . . just don't link any SQL tables . . . =D
0
 
LVL 32

Accepted Solution

by:
bhess1 earned 50 total points
ID: 7073236
Using ASP, hmmm... are you using ADO as the connection method?  If so, what you should use are some ADO Recordset properties:  Pagesize, PageCount, and AbsolutePage

Set Pagesize to the number of records you need per page:

rs.Pagesize = 10

Use pagecount to determine the number of pages in the recordset:

NumPages = rs.PageCount

Use AbsolutePage to go to the appropriate page in the recordset.  For example, to display records 31-40 (page 3):

rs.PageSize = 10
rs.AbsolutePage = 3

For iLoop = 1 to rs.PageSize
   <Display Records here>
   rs.Movenext
Next iLoop

This will display records 31 to 40
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7073305
That's cool Bhess1! . . . It pays to your ADO =)

Makes sense though. If you consider that an ADO recordset is really just a cursor anyway, then there should be a builtin paging fucntionality . . .
0
 

Author Comment

by:dbnewbie
ID: 7074154
bhess, i will try Pagesize, PageCount, and AbsolutePage.
0
 
LVL 32

Expert Comment

by:bhess1
ID: 7074178
It sure simplified our coding when doing paging on our internal website!  We were *really* glad that we found it.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question