IF @currID <> @prevID - 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)

IF @currID <> @prevID

Author

Topic

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-12-02 : 08:26:09

Hi, another basic question. I would like to loop records but reset counter if grouped column changes.

ID Col1
1 chevy
1 chevy
2 ford
2 ford
3 toyota
3 toyota
3 toyota


DECLARE @counter int
DECLARE @_ID int
DECLARE @currID int
DECLARE @prevID int
SET @counter = 0

SELECT @_ID = ID FROM @Table

SET @currID = @_ID

IF @currID <> @prevID
  BEGIN
    -- do process
    SET @prevID = @currID
    SET @counter = @counter + 1
  END
ELSE
  BEGIN
    SET @counter = 0
  END

Am I in the right direction? Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-02 : 08:31:00

[code]DECLARE @Sample TABLE
(
ID INT PRIMARY KEY CLUSTERED,
Col1 VARCHAR(20),
grp INT
)

INSERT @Sample
(
ID,
Col1
)
SELECT 1, 'chevy' UNION ALL
SELECT 2, 'chevy' UNION ALL
SELECT 3, 'ford' UNION ALL
SELECT 4, 'ford' UNION ALL
SELECT 5, 'toyota' UNION ALL
SELECT 6, 'toyota' UNION ALL
SELECT 7, 'toyota'

SELECT *
FROM @Sample

DECLARE @Col1 VARCHAR(20),
@grp INT

UPDATE @Sample
SET @grp = grp = CASE WHEN @Col1 = Col1 THEN @grp ELSE COALESCE(@grp, 0) + 1 END,
@Col1 = Col1

SELECT *
FROM @Sample[/code]

E 12°55'05.63"
N 56°04'39.26"

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-12-02 : 08:46:04

Thanks Peso!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources