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. 12 23284
>> 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"
--
Anith
Where do you want to show the data?
Use Front End application to do this
Madhivanan
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"
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 (an******@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, es****@sommarskog.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/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 (an******@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.
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.
Doug (dr*********@hotmail.com) writes: 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.
There is no guarantee that it will work any version of SQL Server. In fact
for a result set of any size, I would not expect it to work.
What is guaranteed to work, at least in SQL 2005, is if you have a
table with an IDENTITY table, and perform an INSERT with an ORDER BY.
Note that this does not apply to SELECT INTO with the IDENTITY function
and ORDER BY. In that case, there is *no* guarantee.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
Your answer is better - have the identity already there.
But, at least we "solved" the problem!!!!
Hey Andrew
Nothing is impossible, maybe I have read too fast but here is how I would do it.
Sounds like your boss just wants row numbering on your result set.
In reporting services use this expression =RowNumber("DataSetName")
That would be like using Crystal's RecordNumber
Hope this helps
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Rowan Chapman |
last post by:
Hey all!
I'm kinda new to VB but not to programin'.
So I know what it is like when you are asked trivial questions.
Could some1 please tell me what the syntax would be 2 only allow numerical
data...
|
by: David Berry |
last post by:
Hi All. I'm trying to write an ASP page that shows me the UNIQUE account number for a customer (so I can pass it to another page) based on a search criteria. For example, I want to do a select...
|
by: Christian Kienert |
last post by:
Hi,
We are just discussing "you can do almost everything
using a view". Of course the view might become a large
one, but it should be possible to do it with a view.
Any ideas on how to write...
|
by: Jenn L |
last post by:
I have a database that is pre-populated with sequential part numbers.
As people reserve the parts I update a flag to show the # is no longer
available. Now they want the ability to take out a...
|
by: Kif |
last post by:
Hello!
Thanks for the previous help! Now, I have found the SSN-mask, but I need
some help scripting it to controll the numbers. That you cant write month 13
for example or day 39 in a month, and...
|
by: Dave |
last post by:
I'm trying to show line numbers in the vb.net IDE (2003 version on XP Pro).
Am I out of luck? I can't find the setting.
I can't use the debug mode either, but that's a problem for another day.
...
|
by: Sri |
last post by:
How do you add an n-bit number in C?
Regards,
Sri
|
by: serge |
last post by:
/*
Subject: How to build a procedure that returns different
numbers of columns as a result based on a parameter.
You can copy/paste this whole post in SQL Query Analyzer
or Management Studio...
|
by: Nico VanHaaster |
last post by:
Hello,
I don't really want to get too much of a debate here, but I have a
feeling this just might cause one. I have been trying to increase the
performance of one of my applications. (C# .Net...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |