Adding Row Numbers To a SELECT Query Result in SQL Server Without use Row_Number() function

Question

Hi, i need Add Row Numbers To a SELECT Query without using Row_Number() function.
and without using user defined functions or stored procedures.

Select (obtain the row number) as [Row], field1, field2, fieldn from aTable

UPDATE

i am using SAP B1 DIAPI, to make a query , this system does not allow the use of rownumber() function in the select statement.

Bye.

Answer 1

I'm not sure if this will work for your particular situation or not, but can you execute this query with a stored procedure? If so, you can:

A) Create a temp table with all your normal result columns, plus a Row column as an auto-incremented identity.

B) Select-Insert your original query, sans the row column (SQL will fill this in automatically for you)

C) Select * on the temp table for your result set.

Not the most elegant solution, but will accomplish the row numbering you are wanting.

Answer 2

RRUZ, you might be able to hide the use of a function by wrapping your query in a View. It would be transparent to the caller. I don't see any other options, besides the ones already mentioned.

Answer 3

This query will give you the row_number,

SELECT 
    (SELECT COUNT(*) FROM @table t2 WHERE t2.field <= t1.field) AS row_number,
    field,
    otherField
FROM @table t1

but there are some restrictions when you want to use it. You have to have one column in your table (in the example it is field) which is unique and numeric and you can use it as a reference. For example:

DECLARE @table TABLE
(
    field INT,
    otherField VARCHAR(10)
)

INSERT INTO @table(field,otherField) VALUES (1,'a')
INSERT INTO @table(field,otherField) VALUES (4,'b')
INSERT INTO @table(field,otherField) VALUES (6,'c')
INSERT INTO @table(field,otherField) VALUES (7,'d')

SELECT * FROM @table

returns

field | otherField
------------------
1     | a
4     | b
6     | c
7     | d

and

SELECT 
    (SELECT COUNT(*) FROM @table t2 WHERE t2.field <= t1.field) AS row_number,
    field,
    otherField
FROM @table t1

returns

row_number | field | otherField
-------------------------------
1          | 1     | a
2          | 4     | b
3          | 6     | c
4          | 7     | d

This is the solution without functions and stored procedures, but as I said there are the restrictions. But anyway, maybe it is enough for you.