Click here to Skip to main content
14,304,158 members
Rate this:
Please Sign up or sign in to vote.
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:
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
Updated 3-Feb-12 9:38am
v2
Rate this:
Please Sign up or sign in to vote.

Solution 1

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:
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:
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
   
v3
Comments
Sridhar Patnayak 3-Feb-12 6:58am
   
Good one 5+
Amir Mahfoozi 4-Feb-12 0:24am
   
+5
Rate this:
Please Sign up or sign in to vote.

Solution 2

try this

select top 100 * from tblactivity where activityid > 140
   
Rate this:
Please Sign up or sign in to vote.

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[^]
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100