|
David Lundell, MBA Principal Consultant & Trainer |
|
SQL 2000 and 2005 How to use Row Numbering to get every other row |
|
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 |
