How do you generate a sequential number within a group?
-
I need to generate a seqence number based on 2 grouping columns (company and ordernum in this exampe).
Sample Table
Company OrderNum Sku seq_num
A 1 123
A 1 234
B 2 123
So I need to create (or update) the table that has 1 and 2 in the seq_num for the first 2 rows and a 1 in the seq_num in the 3 row. This should be simple, but I'm drawing a blank.
Thanks
-
in a stored proc...
declare @t_seq table(Company char(1), OrderNum int, Sku int, seq_num int identity)
insert into @t_seq
select ...
select * from @t_seq
Jon
-
Well, it doesn't get 'simple' until SQL Server 2005, when we get a ROW_NUMBER() function.
However, in the meantime you can do like this:
create table #x
( company char(1) not null,
ordernum int not null,
sku int not null
)
insert #x select 'A', 1, 123
insert #x select 'A', 1, 234
insert #x select 'B', 2, 123
go
select company,
ordernum,
sku,
seq_num = ( select count(*)
from #x x2
where x2.company = x1.company
and x2.ordernum = x1.ordernum
and x2.sku <= x1.sku
)
from #x x1
go
drop table #x
go
company ordernum sku seq_num
------- ----------- ----------- -----------
A 1 123 1
A 1 234 2
B 2 123 1
/Kenneth
-
sorry - didn't read that very well did I
-
Happens to all of us, especially on Fridays
/Kenneth
-
I appreciate the responses, but still have a problem. I'm processing about 30 million rows and the subquery solution has been running now for about an hour and I suspect it will take a really long time. Any other thoughts?
Thanks,
Nancy
-
Is this a one-time operation, or do you plan doing it on a regular basis? 30 million rows will indeed take quite a while...
/Kenneth
-
If you have very large groups and only a small number of them, this could be faster:
create table #x
( company char(1) not null,
ordernum int not null,
sku int not null,
seq_num int null
)
insert #x select 'A', 1, 123, null
insert #x select 'A', 1, 234, null
insert #x select 'B', 2, 123, null
go
declare @i int
select @i = 0
update #x set @i = seq_num = @i + 1
where company = 'A' and ordernum = 1
select @i = 0
update #x set @i = seq_num = @i + 1
where company = 'B' and ordernum = 2
/* Repeat for each combination of company and ordernum (i.e use a cursor)... */
select * from #x
drop table #x
go
One problem, however, is that you cannot control the ordering of seq_num within the group with this query (can someone change it to achieve this?). But maybe this is not important, or maybe the data is implicitly ordered correctly (depending on the clustered index on your table).
-
Been doing some more thinking
Try this:create table #x
( company char(1) not null,
ordernum int not null,
sku int not null
)
create table #y
( company char(1) not null,
ordernum int not null,
sku int not null,
seq_num int identity(1, 1)
)
create table #z
( company char(1) not null,
ordernum int not null,
sku int not null,
seq_num int
)
insert #x select 'A', 1, 123
insert #x select 'A', 1, 234
insert #x select 'B', 2, 123
go
insert into #y (company, ordernum, sku)
select company, ordernum, sku from #x order by company, ordernum, sku
insert into #z select * from #y
update z1 set z1.seq_num = z1.seq_num - z2.minnumber
from #z z1 inner join
(
select company, ordernum, min(seq_num) - 1 as minnumber from #z group by company, ordernum
)
z2
on z1.company = z2.company and z1.ordernum = z2.ordernum
select * from #z
drop table #x
go
drop table #y
go
drop table #z
go
-
Last attempt for now, I think
create table DataTable
( company char(1) not null,
ordernum int not null,
sku int not null
)
go
create clustered index DataTableIndex on DataTable(company, ordernum, sku)
go
create table ResultTable
(
company char(1) not null,
ordernum int not null,
sku int not null,
seq_num int not null,
seq_num_temp int identity(1, 1)
)
go
create clustered index ResultTableIndex on ResultTable(company, ordernum, seq_num_temp)
go
create table GroupTable
(
company char(1) not null,
ordernum int not null,
minnumber int
)
go
create clustered index GroupTableIndex on GroupTable(company, ordernum, minnumber)
go
insert DataTable select 'A', 1, 123
insert DataTable select 'A', 1, 234
insert DataTable select 'B', 2, 123
insert DataTable select 'B', 2, 124
insert DataTable select 'B', 2, 125
go
insert into ResultTable (company, ordernum, sku, seq_num)
select company, ordernum, sku, 0 from DataTable order by company, ordernum, sku
insert into GroupTable
select company, ordernum, min(seq_num_temp) - 1 from ResultTable group by company, ordernum
update r
set r.seq_num = r.seq_num_temp - g.minnumber
from ResultTable r inner join GroupTable g
on r.company = g.company and r.ordernum = g.ordernum
go
select company, ordernum, sku, seq_num from ResultTable
drop table DataTable
go
drop table ResultTable
go
drop table GroupTable
go
-
I really appreciate all the creative solutions to this problem. I'll do some testing for performance and see what happens.
Thanks!
-
Kenneth's solution may be faster than mine, I have absolutely no idea, but please let us know what you find out...
-
Just one more thing!
Make sure you have indexes in place for the joins and that a you are getting seeks over only one table scan!
* Noel -
And if you do SELECT much more often than INSERT,DELETE, UPDATE, create computed column and make it = dbo.Function2ComputeSeqNumber(OrderNum).
Function must be :
SELECT COUNT(*) ...
where OderNum = @OrderNum and ... <= ...
_____________
Code for TallyGenerator
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply