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

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook