Begin Sequential #ing & restart based on grouping
-
I'm trying to find someone who can help me with SQL script on sequential numbering. But, the twist is I need the numbering to restart when a new group hits. See example below. I'm wanting the Column [LINE #] to count sequentially, then begin a new count when a the Order# changes? Can anyone help? Thanks.
Order# item # Line#
1222258 10 1
1222258 15 2
1222258 25 3
3334489 10 1
5556689 25 1
5556689 26 2
-
What front-end do you use?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Query Analyzer. Thanks.
-
Touchè, brilliant answer
Bit rough round the edges, but this might be a solution
set nocount on
if object_id('seq_t')is not null
drop table seq_t
go
create table seq_t( order_no int,item int, line int)
go
insert into seq_t(order_no, item,line) values(1222258,10,1)
insert into seq_t(order_no, item,line) values(1222258,15,2)
insert into seq_t(order_no, item,line) values(1222258,25,3)
insert into seq_t(order_no, item,line) values(3334489,10,1)
insert into seq_t(order_no, item,line) values(5556689,25,1)
insert into seq_t(order_no, item,line) values(5556689,26,2)
SELECT COUNT(*) AS NO, v.order_no, v.item, v.line
FROM seq_t v CROSS JOIN seq_t a
WHERE
(a.order_no=v.order_no
AND
a.item<=v.item)
GROUP BY v.order_no,v.item, v.line
ORDER BY v.order_no,v.item,v.line
set nocount off
NO order_no item line
----------- ----------- ----------- -----------
1 1222258 10 1
2 1222258 15 2
3 1222258 25 3
1 3334489 10 1
1 5556689 25 1
2 5556689 26 2
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thank you Frank. You're awesome!
One more question if you have time. I played around with your script and it works perfect. In some of my data there will be duplications in the item number. Would you suggest I remove duplications before running the script you created? Or is there something that will remove the dups and count all in one bang?
In purposely added a duplicate in your scenario below.
set nocount on
if object_id('seq_t')is not null
drop table seq_t
go
create table seq_t( order_no int,item int)
go
insert into seq_t(order_no, item) values(1222258,10)
insert into seq_t(order_no, item) values(1222258,11)
insert into seq_t(order_no, item) values(1222258,11)
insert into seq_t(order_no, item) values(1222258,12)
insert into seq_t(order_no, item) values(1222258,13)
insert into seq_t(order_no, item) values(1222258,15)
insert into seq_t(order_no, item) values(1222258,16)
insert into seq_t(order_no, item) values(1222258,24)
insert into seq_t(order_no, item) values(1222258,25)
insert into seq_t(order_no, item) values(3334489,10)
insert into seq_t(order_no, item) values(5556689,25)
insert into seq_t(order_no, item) values(5556689,26)
the duplicate messes the count up.
1 1222258 10
6 1222258 11
4 1222258 12
5 1222258 13
6 1222258 15
7 1222258 16
8 1222258 24
9 1222258 25
1 3334489 10
1 5556689 25
2 5556689 26
I can just remove dups first if that's the easiest way.
Thank you, You're great.
-
Well, I would probably wait if someone comes up with a solution to this and if not remove dups. Right out of the head I can't find a solution. However, I keep thinking...
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hehe, posting this on behalf of a very knowledgeable, but shy friend
SELECT IDENTITY(int) AS ID, Order_No, Item
INTO #TempSeq
FROM Seq_T
ORDER BY Order_No, Item
CREATE CLUSTERED INDEX SeqT_ON ON #TempSeq(Order_No)
SELECT Order_No, Item,
(SELECT COUNT(*)
FROM #TempSeq
WHERE Order_No = s.Order_No
AND ID <= s.ID) Line#
FROM #TempSeq s
Order_No Item Line#
----------- ----------- -----------
1222258 10 1
1222258 11 2
1222258 11 3
1222258 12 4
1222258 13 5
1222258 15 6
1222258 16 7
1222258 24 8
1222258 25 9
3334489 10 1
5556689 25 1
5556689 26 2
When you have a large table to process this solution will also perform better than mine with the CROSS JOIN.
As always, more than one way to skin that cat
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thank you for posting on behalf of a shy fried. Also, Thanks Frank. The new script is perfect. I twiqued it a little and here is what I ended up with. By setting the identity column, the results are counted perfectly. Sometimes though, I have dups in the data that I dont want counted, so I added the group by when creating the identity table.
It's perfect. Thanks guys!!! Brilliant minds at work.
set nocount on
drop table #tempseq
go
if object_id('seq_t')is not null
drop table seq_t
go
create table seq_t( order_no int,item int)
insert into seq_t(order_no, item) values(1222258,10)
insert into seq_t(order_no, item) values(1222258,11)
insert into seq_t(order_no, item) values(1222258,11) /*adding a dup here*/
insert into seq_t(order_no, item) values(1222258,12)
insert into seq_t(order_no, item) values(1222258,13)
insert into seq_t(order_no, item) values(1222258,15)
insert into seq_t(order_no, item) values(1222258,16)
insert into seq_t(order_no, item) values(1222258,24)
insert into seq_t(order_no, item) values(1222258,25)
insert into seq_t(order_no, item) values(3334489,10)
insert into seq_t(order_no, item) values(5556689,25)
insert into seq_t(order_no, item) values(5556689,26)
SELECT IDENTITY(int) AS ID, Order_No, Item
INTO #TempSeq
FROM Seq_T
group by order_no,item
ORDER BY Order_No, Item
go
CREATE CLUSTERED INDEX SeqT_ON ON #TempSeq(Order_No)
go
SELECT Order_No, Item,
(SELECT COUNT(*)FROM #TempSeq WHERE Order_No = s.Order_No AND ID <= s.ID) Line#
FROM #TempSeq s
set nocount off
Order_no Item line
-------- ---- -------
1222258 10 1
1222258 11 2 ****notice that the dup is not counted.
1222258 12 3
1222258 13 4
1222258 15 5
1222258 16 6
1222258 24 7
1222258 25 8
3334489 10 1
5556689 25 1
5556689 26 2
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply