Row_number() in SQL 2000 ...
-
To All Wizards and others,
I need to generate a urn, in a on certain fields. Source fields on which to generate row_num like values looks like; e.g.
col1,col2
1,1
1,10
1,2
2,6
2,20
And I want to generate a column (say col3) with values like below.
col1,col2 ,col3
1,1 , 1
1,10 ,2
1,2 ,3
2,6 ,1
2,20 ,2
I hope that clears out my requirements. I have a quite big table so a efficient solution will be needed, as always 🙂
I need to do this in SQL 2000 and now SQL 2005, which do have a row_number() function 🙁
-
Are you basically looking to add an Identity Col?
if so try this
ALTER TABLE dbo.TestTable ADD
Col3 int NOT NULL IDENTITY (1, 1)
GO
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley:w00t:
SQL-4-Life
Posting Best Practices[/url]
Numbers / Tally Tables[/url] -
It looks like you want a sequence within each set of values. Is this something permanent that you need or is it for reporting? Meaning do you want to add a column?
An identity won't work if I am reading your sequence correctly. Instead you'd need to build a subquery to read the values out and order them within the groups. It will end up being a convoluted SQL query if this is for reporting and I hope this isn't run often.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
Sorry about my previous post I didn't read everything you first wrote 🙂
this will work on SQL2005.
SELECT
Col1,Col2,Row_Number() OVER(Partition by col1 ORDER BY Col1)
FROM TEst
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley:w00t:
SQL-4-Life
Posting Best Practices[/url]
Numbers / Tally Tables[/url] -
This can also be accomplished by using a numbers or tally table to ‘expand’ the groupings out. For this to work, you’ll need an Identity column on your table (or you’ll have to read your data into a temp table or table variable with an Identity column). You’ll also need to create either a static Numbers table or create one on the fly in your procedure/script. See this example for creating your numbers table: http://qa.sqlservercentral.com/columnists/mcoles/2547.asp.
DECLARE @Table TABLE (RowID int IDENTITY(1,1), col1 int,col2 int)
INSERT INTO @Table (Col1, Col2)
SELECT 1,1 UNION ALL
SELECT 1,10 UNION ALL
SELECT 1,2 UNION ALL
SELECT 2,6 UNION ALL
SELECT 2,20
SELECT t1.Col1, t1.Col2, t2.SequenceNum
FROM @Table t1
INNER JOIN (
SELECT N.Num as SequenceNum, t.Col1, (MinRow + N.Num) as RowID
FROM dbo.Numbers N
INNER JOIN (
SELECT Col1, COUNT(*) as 'Rows', (MIN(RowID) - 1) as MinRow
FROM @Table
GROUP BY Col1
) t
ON N.Num BETWEEN 0 AND [Rows]
) t2
ON t1.RowID = t2.RowID
-
Using John's good test setup... this is 1 place where a triangular join may work well because the groups indicated by Col1 are so small...
DECLARE @Table TABLE (RowID int IDENTITY(1,1), col1 int,col2 int) INSERT INTO @Table (Col1, Col2) SELECT 1,1 UNION ALL SELECT 1,10 UNION ALL SELECT 1,2 UNION ALL SELECT 2,6 UNION ALL SELECT 2,20
SELECT Col1, Col2, Col3 = (SELECT COUNT(*) FROM @Table ti WHERE ti.Col1 = t.Col1 AND ti.RowID<= t.RowID) FROM @Table t ORDER BY t.Col1,t.RowID
If the groups are large, then I recommend a much different and faster approach then either John or my code using the proprietary UPDATE SET @variable=column=formula method.
--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)
Intro to Tally Tables and Functions -
There is also nothing wrong with inserting into #Table with IDENTITY column (with desired order of records) and returning result from that table.
In fact any of posted solutions creates temp table, it just happened "behind the scene", so performance will not suffer. Probably it will be even a little bit faster.
_____________
Code for TallyGenerator -
Don't forget... Op wants numbers to restart at 1 at the beginning of each group.
--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)
Intro to Tally Tables and Functions -
-- Prepare sample data
DECLARE @Sample TABLE (RowID INT IDENTITY(1,1), col1 int, col2 int)
INSERT @Sample
SELECT 1, 1 UNION ALL
SELECT 1, 10 UNION ALL
SELECT 1, 2 UNION ALL
SELECT 2, 6 UNION ALL
SELECT 2, 20
-- Show the data
SELECT s1.col1,
s1.col2,
s1.RowID - d.col2 + 1
FROM @Sample AS s1
INNER JOIN (
SELECT col1,
MIN(RowID) AS col2
FROM @Sample
GROUP BY col1
) AS d ON d.col1 = s1.col1
ORDER BY s1.RowID
N 56°04'39.16"
E 12°55'05.25" -
Works fine (pretty cool solution, too, Peter) if you guarantee the order by loading a temp table... bit of a problem otherwise...
-- Prepare sample data
DECLARE @Sample TABLE (RowID INT IDENTITY(1,1), col1 int, col2 int)
INSERT @Sample
SELECT 1, 1 UNION ALL
SELECT 2, 6 UNION ALL
SELECT 1, 10 UNION ALL
SELECT 1, 2 UNION ALL
SELECT 2, 20
-- Show the data
SELECT s1.col1,
s1.col2,
s1.RowID - d.col2 + 1
FROM @Sample AS s1
INNER JOIN (
SELECT col1,
MIN(RowID) AS col2
FROM @Sample
GROUP BY col1
) AS d ON d.col1 = s1.col1
ORDER BY s1.RowID
Not bashing code folks... just want you to be aware that the code is very order sensitive.
--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)
Intro to Tally Tables and Functions -
Okay ... so this seems to be more intesting then what i originally thought it would be ... huh ?
Great approch you all. But the problem is still unsolved.
Also the 1st column is not rowid, it could be any value and is not in order !!! ...
Thanks for everyone putting time and effort into this.
-
Well, the sample data was ordered so I thought this was the case in production environment.
Now OP finally seems to have released all information necessary.
N 56°04'39.16"
E 12°55'05.25" -
Heh... and people ask me why I use IDENTITY columns...
FP, think outside the box a bit... all the solutions that use an IDENTITY column work very fast. If your table doesn't have an IDENTITY column to indicate the order, as you say, then even a
Cursor 
wouldn't work for you. You need to identify something in your table that DOES identify the order you want.Once you've done that, copy the data, in the correct order, to a temp table that DOES have an IDENTITY column and use one of the solutions posted... it'll still be faster than a Cursor or While loop.
--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)
Intro to Tally Tables and Functions
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply