Group counter - - 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

Transact-SQL (2000)

Group counter -

Author

Topic

gugarc
Starting Member

17 Posts

Posted - 2006-09-26 : 14:30:07

How do I achieve the following solution in just one query, without subqueries like (select count(*) where id < outer.id and Group=outer.group) ...

table has two columns: ID int identity, SEQ_ID int and Group int, SEQ_ID is Empty, Group is already filled.

SEQ_ID Group
1 1
2 1
3 1
1 2
2 2
...

- I saw recently an extraordinary solution, using something like this :
(declare @inc int
set @inc = 0
UPDATE @table SET @inc = id = @inc + 1 )

but I don´t remember how to make this counter work for groups ...

gugarc
Starting Member

17 Posts

Posted - 2006-09-26 : 14:39:23

I got it ... thx ...

declare @inc int
declare @grp int

set @inc = 0

UPDATE table SET @inc = seq_id = case when @grp=group then @inc + 1 else 1 end, @grp=group

select * from table ...

Kristen
Test

22859 Posts

Posted - 2006-09-26 : 14:41:07

Not really sure what you are trying to do, but you can use something similar to the "extraordinary solution"

along the lines of


declare @inc int,
        @listGroup int  -- Assumed that GROUP column is INT, but any datatype will do
set @inc = 0,
    @listGroup = NULL
UPDATE @table 
SET id = CASE WHEN Group = @listGroup THEN id ELSE @inc + 1,
    @inc = CASE WHEN Group = @listGroup THEN id ELSE @inc + 1,
    @listGroup = Group

You cannot control the order in which the new incremental number is applied to the records though, so more typically you would Insert the existing data into a temporary table, which has an IDENTITY, in the appropriate order; then UPDATE the temporary table to get the new IDs [using the IDENTITY column as a basis for allocating them], and then UPDATE the original table, joining it to the original table [you need the PK columns in the Temporary Table of course!!]

Kristen

gugarc
Starting Member

17 Posts

Posted - 2006-09-26 : 14:57:35

:((
How would this work when the table isn´t ordered by the group_id
column? Both solutions didn´t work ... (the counter got reseted when the group changes ...)

Kristen
Test

22859 Posts

Posted - 2006-09-26 : 15:13:03

I believe there is some way to force a temporary table ORDER BY during an UPDATE, but I can't remember/find the link.

So I reckon you are left with using an IDENTITY column in a temporary table to provide the information - you can use a Self Referencing Join to find the preceding row, where the Group has changed, and set the NewID based on whether it is the same, or not.

You may have to employ a second temporary table to get the NewIDs contiguous - e.g.:

Select all the DISTINCT Group numbers into a temporary table with Identity column (using ORDER BY GROUP so the Identity's are in ascending order by Group)

Join original table to the second temporary table on Group ID and use the Identity in second table as the basis for an update of the first table.

But I'm still guessing what it is that you are trying to achieve!

Kristen

gugarc
Starting Member

17 Posts

Posted - 2006-09-26 : 16:36:39

I just want to enumerate the itens in each group, in any order. But i don´t want to use subqueries - due to performance problems.

Kristen
Test

22859 Posts

Posted - 2006-09-27 : 01:46:28

Well if you use a loop to enumerate the itens in each group it will be a lot slower than using subqueries!

I've only just now seen your second post, which you posted as I was writing my first. That depends on the rows being processed in order by ID, doesn't it?.

This is what I would do, but I imagine there is a more efficient way (apart from just combining some of these steps!!):


DECLARE @Data TABLE
(
	ID	int IDENTITY NOT NULL,
	SEQ_ID	int NULL,
	[Group]	int NOT NULL
)
INSERT INTO @Data(SEQ_ID, [Group])
SELECT	NULL, 1 UNION ALL
SELECT	NULL, 2 UNION ALL	-- Insert [Group] in random order
SELECT	NULL, 1 UNION ALL
SELECT	NULL, 2 UNION ALL
SELECT	NULL, 1

PRINT 'Original data (random order):'
SELECT	* 
FROM	@Data
ORDER BY [Group], SEQ_ID, ID

DECLARE @TEMP TABLE
(
	ID		int IDENTITY NOT NULL,
	OriginalID	int NOT NULL,
	NewSEQ_ID	int NULL,
	[Group]		int NOT NULL
)

INSERT INTO @TEMP(OriginalID, [Group])
SELECT	ID, [Group]
FROM	@Data
ORDER BY [Group], ID

UPDATE T
SET	NewSEQ_ID = T.ID - X.MinID + 1
FROM	@TEMP AS T
	JOIN
	(
		SELECT	[Group],
			[MinID] = MIN(ID)
		FROM	@TEMP
		GROUP BY [Group]
	) AS X
		ON X.[Group] = T.[Group]

UPDATE D
SET	SEQ_ID = T.NewSEQ_ID
FROM	@Data AS D
	JOIN @TEMP AS T
		ON T.OriginalID = D.ID

PRINT 'Updated data:'
SELECT	* 
FROM	@Data
ORDER BY [Group], SEQ_ID, ID

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources