Sequential numbered column
-
I need to create a non-database column in a query that renders a sequential number. Essentially, this column would number the rows... 1,2,3,4,5, etc.
Is it possible or is there a technique to do this?
Thanks!
Mark
-
Post the table definition and some sample data along with the expected results so we can figure out the best solution for you problem.
-
Your Request lacks the detail to produce an accurate answer.
I am guessing here, but I am thinking this is what your looking for:
How to dynamically number rows in a query
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q186133
-
... and the classic answer : do it client side
. -
Ray,
You are right in that the article you quoted shows how to dynamically rank rows. That is what I want to do.
However, I have read that article before and unfortunately that technique works well only when you have hundreds of rows or less. Plus if there are duplicate keys, the ranking doesn't work.
But thanks!
Mark
-
I am just trying to dynamically number the rows in a query without using IDENTITY (which makes me recreate the table).
Mark
-
As previously asked, can you post the table definition, some sample data and the expected output so that we can make the query??
Also why are you generating those numbers?
-
Why??
Application requirement?
Reporting?
Deleting rows?
Deleting duplicates?
-
Remi,
I am populating a DataGrid in VB .NET using a query. I thought it would be nice to have a column with a row number, just like you get in the SQL Query Analyzer. The DataGrid control won't supply that.
I am using a SQLDataFillAdapter to bind the query to the control, since this seems to be much faster than using a SQLDataReader, looping through, building each row, and adding it to the underlying DataTable. If I were willing to loop through I could just assign a counter. But I don't want to do that.
I have had other occasions when I have just wished I could get a row number like you get in SQL Query Analyzer. Except I am not using SQL Query Analyzer.
Does that make sense?
Mark
-
A few things :
1 - Why do you have duplicates in that table?
2 - If this is not a requirement, then why do you complicate your life?
3 - Are you sure that the bind is faster than filling it up manually?
-
IF OBJECT_ID('TempDB..#Results') IS NOT NULL
DROP TABLE #Results
SELECT IDENTITY(INT,1,1) AS RowNum,
...other columns as you need...
INTO #Results
FROM ...your tables and joins...
ORDER BY ...the order you want things numbered...
SELECT *
FROM #Results
ORDER BY RowNum
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
This was only one of the possible solutions... I wanted to get over the fact that there should not be duplicates in a table and that he should explore the client side version first... Then any suggestion on the microsoft side would have worked
.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply