>> In 2000, though, it seems to be utterly impossible. My boss, however, is
>> convinced that there must be some way to create a calculated field to do
>> it.

Anith Sen wrote:
> >> In 2000, though, it seems to be utterly impossible. My boss, however, is
> >> convinced that there must be some way to create a calculated field to do
> >> it.
>
> Paste the following in Google search box:
> "dynamically number rows site:support.microsoft.com"

Madhivanan wrote:
> Where do you want to show the data?
> Use Front End application to do this

On 27 Mar 2006 16:32:09 -0800, Andrew Lias wrote:

>I've been given a task that I believe is, basically, impossible, but
>I'd like to see if there's a way to do it.
>
>What my boss wants me to do is to create a view, in SQL Server 2000,
>that will provide not only a row number field of some sort, but that
>will produce sequential ordering for arbitrary selects and orderings.
>So, if my data is a table with values from A thru D and my user does
>SELECT data FROM vwTable, the result would be:
>
>Row Data
>--- ----
>1 A
>2 B
>3 C
>4 D
>
>But is they did SELECT data FROM vwTable ORDER BY data DSC, they would
>get
>
>Row Data
>--- ----
>1 D
>2 C
>3 B
>4 A
>
>And if the did SELECT data FROM vwTable WHERE Data IN ('B', 'C'), they
>would get
>
>Row Data
>--- ----
>1 B
>2 C
>
>In SQL 2005, of course, this would be fairly trivial since I could use
>the ROW_NUMBER function. In 2000, though, it seems to be utterly
>impossible. My boss, however, is convinced that there must be some way
>to create a calculated field to do it.
>
>I'll be cursed if I can figure out a way to do so.
>
>Any suggestions would be appreciated.

> Thanks, however, while that is a good way to derive row numbers in a
> select statement, unfortunately it isn't quite what my boss is asking
> me to do. She wants a view that will produce row counts in a
> calculated field regardless of the order that the user uses to select
> the data.

>>SQL Reporting Services

Erland Sommarskog wrote:
> Andrew Lias (anrwlias@gmail.com) writes:
> > Thanks, however, while that is a good way to derive row numbers in a
> > select statement, unfortunately it isn't quite what my boss is asking
> > me to do. She wants a view that will produce row counts in a
> > calculated field regardless of the order that the user uses to select
> > the data.
>
> Time to get a new boss?
>
> What she is asking for is not possible. You would have to package the
> user's SELECT statement somehow, so you can modify to add the row-number
> column. As Hugo pointed out, this is the same on SQL 2005.

> if you can use a stored procedure instead of a view, you could select
> the data INTO a temp table in the "correct order", alter the table to
> add an identity column, and return that ordered by identity.
> before someone gets excited, there isn't a GUARANTEE this will work
> forever in future versions of SQL, but it probably will.