Row Numbers for a View

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.

>> In 2000, though, it seems to be utterly impossible.  My boss, however, is
 

Paste the following in Google search box:
"dynamically number rows site:support.microsoft.com"

--
Anith

Where do you want to show the data?
Use Front End application to do this

Madhivanan

Anith Sen  wrote: 
>
> Paste the following in Google search box:
> "dynamically number rows site:support.microsoft.com"

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.

I would prefer to require the user to generate the row numbers in their
selects, wjhich wouldd allow for the solution you offered.
Unfortunately, that isn't what I've been tasked to do.

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

SQL Reporting Services.

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.

Hi Andrew,

The way you describe it here, it's impossible. That holds true for both
SQL Server 2005 and SQL Server 2000. Even ROW_NUMBER() won't help you.

If you need the row numbers to match the order specifiede on the select
and if you want to skip numbers for rows not included in the select,
you'll have to add row numbering logic on the SELECT statement. If you
add row numbers in the view, the numbers won't change if you exclude
some rows or choose a different order when selecting from the view.

Just to prevent misunderstanding - it is NOT impossible to get the
result sets you require. But it's only possible by extending the SELECT
with some row numbering logic. Either using ROW_NUMBER() if you're using
SQL Server 2005, or by using either a correlated subquery or a self-join
and a GROUP BY if you're using SQL Server 2000.

--
Hugo Kornelis, SQL Server MVP

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.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

>>SQL Reporting Services

Cant you make use of Recordnumber feature such as the one available in
Crystal reports?

Madhivanan

Erland Sommarskog  wrote:
> Andrew Lias (anrwlias@gmail.com) writes: 
>
> 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.

That's what I thought.  I just wanted to be extra sure that there
wasn't some tricky way to do this before I went back to her and said
that it simply could not be done the way that she was asking.

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.