Retrieve Records from Row X to Row Y From a SQL Server Table that does not have an ID column

In one of my previous posts, I had discussed How to retrieve record from a specific row for tables that do not have an ID. In this post, I will show you how to retrieve records between two Row Numbers from a SQL Server 2005/2008 table

Taking the example of the Northwind database, the table Customer has a primary key that is non-numeric. So in order to find the rows between position 10 and 20 (ordered by CustomerID), we will use a CTE (Common Table Expression) to do so. Here’s the query:

USE NORTHWIND
GO
DECLARE @Start as smallint = 10;
DECLARE @End as smallint = 20;

WITH CTE AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY CustomerID) AS cid,
    CustomerID, CompanyName           
    FROM Customers
)
SELECT cid as ID, CustomerID,  CompanyName   
FROM CTE
WHERE cid BETWEEN @Start AND @End

OUTPUT

Did you like this post?

	subscribe via rss		subscribe via e-mail
	print this post		follow me on twitter

Feedback: