Hi
I am using SQL Server 7.0 to create Web based reports for global clients using Business Objects. I need to show line numbers(row numbers) for each record retrieved. Is there any function to achieve this in SQL(parallel to Oracle's rank() over rowid function) ? Any kinda help will be appreciated

Well I ran into a situation where I needed a sequence number (or row number)
and couldn't find any function which would do it so I devised my own method
of obtaining it.

In this example I have a table called Export with an Identity field called
pkID
I do a nested select counting the prior records(including the current rec)

Select productionnumber,(Select count(pkID) from Export Cnt Where
Cnt.pkID<=Export.pkID and ordernumber='mo123') as Seq from export where
ordernumber='mo123'

The select statement returns
ProductionNumber & Seq for OrderNumber 'mo123'

so heres how the nested select responds if
pkID Seq
10 1
12 2
15 3
18 4
20 5

I've done this same procedure with non - identity fields too.

If you want help with the sql statement, give me some details on the fields
& table name and I'll help you create one

Maurice


[quoted text, click to view]
using Business Objects. I need to show line numbers(row numbers) for each
record retrieved. Is there any function to achieve this in SQL(parallel to
Oracle's rank() over rowid function) ? Any kinda help will be appreciated.
[quoted text, click to view]

Refer to:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q186133

--
Anith