Numbering rows in a view

I am connecting to an SQL database using a PLC, and need to return a list of values. Unfortunately, the PLC has limited memory, and can only retrieve approximately 5,000 values at any one time, however the database may contain up to 10,000 values.

As such I need a way of retrieving these values in 2 operations. Unfortunately the PLC is limited in the query it can perform, and is limited to only SELECT and WHERE commands, so I cannot use LIMIT or TOP or anything like that.

Is there a way in which I can create a view, and auto number every field in that view? I could then query all records < 5,000, followed by a second query of < 10,000 etc?

Unfortunately it seems that views do not support the identity column, so this would need to be done manually.

Anyone any suggestions? My only realistic option at the moment seems to be to create 2 views, one with the first 5,000 and 1 with the n开发者_StackOverflowext 5,000...

I am using SQL Server 2000 if that makes a difference...

There are 2 solutions. The easiest is to modify your SQL table and add an IDENTITY column. If that is not a possibility, the you'll have to do something like the below query. For 10000 rows, it shouldn't be too slow. But as the table grows, it will become worse and worse-performing.

SELECT        Col1, Col2, (SELECT COUNT(i.Col1) 
               FROM yourtable i 
               WHERE i.Col1 <= o.Col1) AS RowID
FROM          yourtable o

While the code provided by Derek does what I asked - i.e numbers each row in the view, the performance for this is really poor - approximately 20 seconds to number 100 rows. As such it is not a workable solution. An alternative is to number the first 5,000 records with a 1, and the next 5,000 with a 2. This can be done with 3 simple queries, and is far quicker to execute.

The code to do so is as follows:

SELECT TOP(5000) BCode, SAPCode, 1 as GroupNo FROM dbo.DB 
UNION
SELECT TOP (10000) BCode, SAPCode, 2 as GroupNo FROM dbo.DB p 
WHERE ID NOT IN (SELECT TOP(5000) ID FROM dbo.DB)

Although, as pointed out by Andriy M, you should also specify an explicit sort, to ensure the you dont miss any records.

One possibility might be to use a function with a temporary table such as

CREATE FUNCTION dbo.OrderedBCodeData()
RETURNS @Data TABLE (RowNumber int IDENTITY(1,1),BCode int,SAPCode int)
AS 
BEGIN
    INSERT INTO @Data (BCode,SAPCode)
    SELECT BCode,SAPCode FROM dbo.DB ORDER BY BCode

    RETURN
END

And select from this function such as

SELECT FROM dbo.OrderedBCodeData() WHERE RowNumber BETWEEN 5000 AND 10000

I haven't used this in production ever, in fact was just a quick idea this morning but worth exploring as a neater alternative?