| 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 |
|
|
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 |
|
|
|
row_number() equivalent