row_number() equivalent - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

row_number() equivalent

Author

Topic

markj11
Starting Member

17 Posts

Posted - 2009-08-27 : 12:48:05

[code]insert into tableA
(colA, colB, colC)
values
(select colA, colB, ?IDENTITY? as colC
from tableB)[/code]

desired results:
[code]
colA colB colC
A B 1
C D 2
E F 3[/code]

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-08-27 : 13:04:01

You need to define colC as an IDENTITY column..You dont have to worry
about it during insert.

CREATE TABLE [dbo].[tableA](
[colA] varchar(10) NOT NULL,
[colB] varchar(10) NOT NULL,
[colC] [int] IDENTITY(1,1) NOT NULL
)

markj11
Starting Member

17 Posts

Posted - 2009-08-27 : 13:17:39

Sorry my question was not clear.

expected results would actually be:


colA   colB   colC
A      B      1
A      C      2
B      A      1
B      B      2

EDIT: just realized I had a similar problem a while back.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=95690

markj11
Starting Member

17 Posts

Posted - 2009-08-27 : 13:28:57

Answer:


insert into tableA 
   (colA, colB, colC)
values 
   (select colA, colB, 
      row_number() over(partition by colA order by colA) as colC
   from tableB)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-27 : 13:50:45

The ranking functions (like row_number) is not available in 2000. Perhaps you just posted this in the wrong forum.

Be One with the Optimizer
TG

markj11
Starting Member

17 Posts

Posted - 2009-08-27 : 15:41:58

quote:
Originally posted by TG

The ranking functions (like row_number) is not available in 2000. Perhaps you just posted this in the wrong forum.

Be One with the Optimizer
TG

Ok, I guess this is not resolved. I need to make this work in 2000 this time.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-27 : 15:57:07

So the next question is what are you using this column for? If it is uniqueness than why not let it be an identity and not reset for each grouping? There have been more than a few topics about this - I'll see if I can find any...

EDIT:
found one:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=45429&SearchTerms=display,order

Be One with the Optimizer
TG

markj11
Starting Member

17 Posts

Posted - 2009-08-27 : 16:03:06

quote:
Originally posted by TG

So the next question is what are you using this column for? If it is uniqueness than why not let it be an identity and not reset for each grouping? There have been more than a few topics about this - I'll see if I can find any...

Be One with the Optimizer
TG

Yeah, the scope of the problem changed once I realized that there could multiple colA with differnt colB. I need to change the title. I need it be more of a record counter based on colA.

markj11
Starting Member

17 Posts

Posted - 2009-08-27 : 16:04:49

quote:
Originally posted by TG

So the next question is what are you using this column for? If it is uniqueness than why not let it be an identity and not reset for each grouping? There have been more than a few topics about this - I'll see if I can find any...

EDIT:
found one:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=45429&SearchTerms=display,order

Be One with the Optimizer
TG

Thanks, that guy explained the problem better than me.

markj11
Starting Member

17 Posts

Posted - 2009-08-27 : 16:54:44

Ok, here's what is close from the other thread:


declare @table table (Orderid int, Descr varchar(15))

INSERT INTO @table(OrderID, Descr)
SELECT 1 , 'Apple'  UNION ALL
SELECT 1 , 'Orange' UNION ALL
SELECT 2 , 'Pear'   UNION ALL
SELECT 2 , 'Apple'  UNION ALL
SELECT 2 , 'Grape'

Select	OrderID
	,Descr
	,ItemNo = (Select count(*) 
		from @table 
		where Descr <= t.descr 
		and [orderid] = t.[orderid])
From	@table t
Order by OrderID

Returns:


OrderId   Descr   ItemNo
1         Apple   1
1         Orange  2
2         Pear    1
2         Apple   2
2         Grape   3

What I need is:


declare @table table (Orderid int, Descr varchar(15), Amt money)

INSERT INTO @table(OrderID, Descr, Amt)
SELECT 1 , 'Apple', .50  UNION ALL
SELECT 1 , 'Apple', .50  UNION ALL
SELECT 1 , 'Orange', .50 UNION ALL
SELECT 2 , 'Pear', .50   UNION ALL
SELECT 2 , 'Apple', .50  UNION ALL
SELECT 2 , 'Grape', .50

Select	OrderID
	,Descr
	,ItemNo = (Select count(*) 
		from @table 
		where Descr <= t.descr 
		and [orderid] = t.[orderid])
   ,SUM(Amt)
From	@table t
Group by OrderID, Descr
Order by OrderID

Results:


OrderId   Descr   ItemNo   Amt
1         Apple   2        1.00
1         Orange  3        0.50
2         Pear    1        0.50    
2         Apple   2        0.50
2         Grape   3        0.50

The ItemNo is off b/c the 2 Apples in order 1. I tried to add a group by Descr to the subselect but that did not work.

markj11
Starting Member

17 Posts

Posted - 2009-08-27 : 17:39:44

I got it:


declare @table table (Orderid int, Descr varchar(15), Amt money)

INSERT INTO @table(OrderID, Descr, Amt)
SELECT 1 , 'Apple', .50  UNION ALL
SELECT 1 , 'Apple', .50  UNION ALL
SELECT 1 , 'Orange', .50 UNION ALL
SELECT 2 , 'Pear', .50   UNION ALL
SELECT 2 , 'Apple', .50  UNION ALL
SELECT 2 , 'Grape', .50

Select	OrderID
	,Descr
	,ItemNo = (Select count(distinct descr) 
		from @table 
		where Descr <= t.descr 
		and [orderid] = t.[orderid])
   ,SUM(Amt)
From	@table t
Group by OrderID, Descr
Order by OrderID

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-28 : 02:06:05

Quirky updates are useful and effecient
http://sqlblogcasts.com/blogs/madhivanan/archive/2009/06/10/quirky-update-in-sql-server.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources