Sequencing By Group - 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)

Sequencing By Group

Author

Topic

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-31 : 20:57:34

http://www.sqlteam.com/item.asp?ItemID=765

I've used the idea in this article and expanded it to solve a problem I encountered. I needed to create a sequencial number for each record in a table base on a column. The sequence needs to start from 1 for each new group.

Here is what I have so far and I was wondering if someone as something simpler.


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Ordered]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Ordered]
GO

CREATE TABLE [dbo].[Ordered] (
	[ColA] [int] NOT NULL ,
	[ColB] [int] NOT NULL ,
	[OrderByColA] [int] NULL 
) ON [PRIMARY]
GO


INSERT INTO dbo.Ordered(ColA,ColB)
VALUES (1,22)
INSERT INTO dbo.Ordered(ColA,ColB)
VALUES (1,27)
INSERT INTO dbo.Ordered(ColA,ColB)
VALUES (1,88)
INSERT INTO dbo.Ordered(ColA,ColB)
VALUES (2,1)
INSERT INTO dbo.Ordered(ColA,ColB)
VALUES (2,77)

DECLARE @intCounter int
SET @intCounter = 0
UPDATE  dbo.Ordered
SET @intCounter = OrderByColA = @intCounter + 1


UPDATE dbo.Ordered
SET OrderByColA = DD.TrueOrder
FROM
dbo.Ordered CC LEFT JOIN
(
SELECT 
BB.ColA, 
BB.ColB,
CASE
WHEN BB.TrueOrder Is Null THEN BB.OrderByColA
ELSE BB.TrueOrder
END As TrueOrder
FROM
(
SELECT A.ColA, A.ColB, A.OrderByColA, B.MaxOfOrderByColA, (A.OrderByColA - B.MaxOfOrderByColA) As TrueOrder
FROM dbo.Ordered A LEFT JOIN
(SELECT AA.ColA, Max(AA.OrderByColA) As MaxOfOrderByColA 
 FROM dbo.Ordered AA 
 GROUP BY AA.ColA) B ON A.ColA = B.ColA + 1
) BB 
) DD ON CC.ColA = DD.ColA And CC.ColB = DD.ColB

SELECT * FROM Ordered

Another problem I'm having is that ColA is not sequencial so
A.ColA = B.ColA + 1 is nothing going to work
I'm trying a correlated sub query but I just can't get it.

I've been trying this for the inner part but it's not right.


SELECT A.ColA, A.ColB, A.OrderByColA, B.ColA, B.MaxOfOrderByColA, (A.OrderByColA-B.MaxOfOrderByColA) As TrueOrder
FROM dbo.Ordered A,
(SELECT AA.ColA, Max(AA.OrderByColA) As MaxOfOrderByColA 
 FROM dbo.Ordered AA 
 GROUP BY AA.ColA
 ) B 
WHERE B.ColA = (SELECT MIN(C.ColA) FROM dbo.Ordered C WHERE C.ColA < A.ColA) AND A.ColA <> B.ColA

Here are the values for this


INSERT INTO dbo.Ordered(ColA,ColB)
VALUES (1,22)
INSERT INTO dbo.Ordered(ColA,ColB)
VALUES (1,27)
INSERT INTO dbo.Ordered(ColA,ColB)
VALUES (1,88)
INSERT INTO dbo.Ordered(ColA,ColB)
VALUES (21,1)
INSERT INTO dbo.Ordered(ColA,ColB)
VALUES (21,77)
INSERT INTO dbo.Ordered(ColA,ColB)
VALUES (32,1)
INSERT INTO dbo.Ordered(ColA,ColB)
VALUES (32,77)

The correct result should be

1 1
1 2
1 3
21 1
21 2
32 1
32 2

If anyone can help me see the light on this it would be great.
Thanks in Advance.

Edited by - ValterBorges on 10/31/2002 21:53:42

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-31 : 23:02:36

Take a look here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=19066

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-31 : 23:25:25

Excellent. Thanks Rob

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources