Abstract: Sometimes you just need to extract every other row from a result set. Other times you need a row number so as to provide paging in a web application.

Here is an example of how to extract every other row from a result set in both SQL Server 2000 and SQL Server 2005

Body:

Possible shortcut: If the underlying result set has an integer that is more or less evenly divided between odd and even then this example with the Categories table in the Northwind database will work.

SELECT CategoryID, CategoryName

FROM dbo.Categories

WHERE CategoryID % 2 = 1

By using the % (modulo) operator we get rows with the categoryid is odd. This does not perfectly give you everyother row since we could have a missing value (the row could have been deleted). Even worse, your additional search criteria could select data that is not evenly distributed between even and odd ids.

This will work every time:

DECLARE @tCat TABLE (TID int identity(1,1), CategoryID int, CategoryName varchar(100))

INSERT @tCat (CategoryID, CategoryName )

SELECT CategoryID, CategoryName

FROM dbo.Categories

SELECT CategoryID, CategoryName

FROM @tCat

WHERE TID % 2 = 1

In SQL 2005  you could use the Row_Number() function.

SELECT CategoryID, CategoryName

FROM (SELECT Row_Number() OVER(ORDER BY CategoryName DESC) as RowNum, CategoryID, CategoryName

                                  FROM dbo.Categories

) Cat

WHERE RowNum % 2 = 1