How to write the query for this scenario?

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi,
I have the following table called tblactivity with the following structure:

activityid(int),name(string).

Now,I want to get next 100 records from this whose activityid is greater than a given activityid[say 140].

If I have written the query like this:

SQL

select * from tblactivity where activityid>'140' and activityid<'240'

It wont work all the time as because there might have been records deleted within that range.So it can happen that after 140 the next activityid that is existing in tblactivity is 2000.
So what I need to fetch is next 100 records from tblactivity regardless of their activityid,but it should be greater than 140.

Thanks,
Avishek

Posted 3-Feb-12 0:44am

avishekrc

Updated 3-Feb-12 9:38am

Wendelius

v2

Add a Solution

3 solutions

Solution 2

try this

SQL

select top 100 * from tblactivity where activityid > 140

Posted 3-Feb-12 0:55am

RDBurmon

Solution 3

To add to the correct answer by Manfred R. Bihy it sounds like you're doing paging. If that's the case, it's a very common scenario which can be done in several ways. Few more links to look at:
- Paging in SQL Server 2005[^]
- Paging Records Using SQL Server 2005 Database - ROW_NUMBER Function[^]

Posted 3-Feb-12 9:45am

Wendelius

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Manfred Rudolf Bihy · Accepted Answer · 2012-02-03T00:54:00

You want to look into the TOP(n)[^] clause of TSQL[^].

Here's the idea assuming activityids are only positive integers including zero:

Select the first 100 activity entries:

SQL

SELECT TOP(100) *
FROM tblactivity
WHERE activityid > -1
ORDER BY activityid ASC

For the next 100 entries lets assume the last activityid was 155 then you do this:

SQL

SELECT TOP(100) *
FROM tblactivity
WHERE activityid > 155 -- If 155 was the last activityid from the previous select statement
ORDER BY activityid ASC

So you always use the last activityid with the "greater than" relational operator and the TOP(100) clause and your all set and ready to go. When you want to start from the first activity you assume the last activityid was -1.

Regards,

Manfred