|
|
Posted by Hugo Kornelis on 10/01/81 11:43
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
Navigation:
[Reply to this message]
|