How to select the next top rows including the new rows that User added between the selection.

Assume I have a table ordered by Name column. At the first time I'd like to select the top 500 rows. User can add new rows to the table. Based on user requirements. I'd like to retrieve the next 500 rows without retrieving the first 500 rows again. Assume that table is order by name and he added new rows that might be at the top 500.

The question is How can I select the next 500 rows including the new rows that I couldn't get at the first tim开发者_JAVA百科e because it's new rows?

What you're describing is called Paging

Here's a nice article that describes it. Server Side Paging using SQL Server 2005

Which includes this sample

DECLARE @PageSize INT,
    @PageNumber INT,
    @FirstRow INT,
    @LastRow INT

SELECT  @PageSize = 20,
    @PageNumber = 3

SELECT  @FirstRow = ( @PageNumber - 1) * @PageSize + 1,
    @LastRow = (@PageNumber - 1) * @PageSize + @PageSize ;

WITH Members  AS
(
    SELECT  M_NAME, M_POSTS, M_LASTPOSTDATE, M_LASTHEREDATE, M_DATE, M_COUNTRY,
            ROW_NUMBER() OVER (ORDER BY M_POSTS DESC) AS RowNumber,
            ROW_NUMBER() OVER (ORDER BY M_NAME DESC) AS RowNumber2
    FROM    dbo.FORUM_MEMBERS
)
SELECT  RowNumber, M_NAME, M_POSTS, M_LASTPOSTDATE, M_LASTHEREDATE, M_DATE, M_COUNTRY
FROM    Members
WHERE   RowNumber BETWEEN @FirstRow AND @LastRow
ORDER BY RowNumber ASC;

Note the pagesize and pagenumber variables. These could be parameters to a stored procedure instead.

Im assming that u have a column in users table called isnewuser which is set to true for everynew user added and is not shown in the list

while you are viewing next 500 records, while some other users have been added, i would suggest you to show them in seperate list below the original one saying " new users" etcc etc..

Its makes no sense to show newly added users, which could have been on first page , on page 2 of main list .