Getting the Next Set of Results

Getting the Next Set of Results

I’m getting the first “page” of data from a table and I’m wondering about getting the next page…

I’m using the query:

SELECT TOP 50 ID, Name, Address, Phone, RankFROM Person, FileWHERE Person.RankID = File.RankID AND Name LIKE 'R%'ORDER BY Name ASC

I would like to then be able to retrieve the next 50 records, preferably without using cursors.

Is there any way to do this?

PS. I am using SQL Server 7.0.

Well, the normal way of traversing a table without cursors would be to have a line in the WHERE clause that limits the rows returned to ones that you haven’t seen yet. In your example, let’s say that the last ID returned was 50; you would addwhere id > 50 to obtain your next result set.

The problem is how to do this programmatically. If you are sending the result set back to a front end where you can then capture the last row and modify your SQL dynamically, you are in luck.

However, for the purposes of this answer, I will stick to pure T-SQL solutions.

If I were processing one row at a time, I could do the following (using pubs..authors as an example):

declare @id char(30) select @id = min (au_id)from authorsselect * from authorswhere au_id = @idwhile @id is not nullbegin     print 'Do whatever processing you need to for the row'     select @id     select @id = min(au_id) from authors where au_id > @idend

However, it is illegal to say “select @id = top 3 au_id …” etc., so there is no easy way to jump by units of 50.

Share the Post:
Heading photo, Metadata.

What is Metadata?

What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular

XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several

data fivetran pricing

Fivetran Pricing Explained

One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of

kubernetes logging

Kubernetes Logging: What You Need to Know

Kubernetes from Google is one of the most popular open-source and free container management solutions made to make managing and deploying applications easier. It has a solid architecture that makes