Grouping data - 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)

Grouping data

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2006-04-19 : 10:15:16

Guys

I have following data

INUM IGRP
____________
3A 510000
3A 510000
3010 510001
3010 510001

You would notice that INUM column has varchar values, so I am trying to create populate a column IGRP (INT)
with corresponding numeric values which I generate based on the following logic

CREATE PROCEDURE [ntest97nos] AS

DECLARE @totalRecords INT
DECLARE @ctr INT, @key INT, @subID varchar(20), @subid_prev varchar(20)
SET @ctr = 1
SELECT @key = 510000
SELECT @totalRecords = MAX([ID]) FROM [tablea]

WHILE @ctr <= @totalRecords
BEGIN
SELECT @subID = INUM
FROM [tablea]
WHERE [ID] = @ctr
ORDER BY INUM
IF @subID <> @subid_prev SET @key = @key + 1

---- Updates the Table
UPDATE [tablea] SET igrp = @key
WHERE [ID] = @ctr

--increment the counter
SET @ctr = @ctr + 1
SET @SUBID_PREV = @SUBID
END
GO

after I run the stored procedure count from the below 2 queries are not matching, which you expect to match

SELECT DISTINCT INUM FROM TABLEA
SELECT DISTINCT IGRP FROM TABLEA

Am I missing a trick here

Any suggestions/inputs as to why SP is not running correctly

Thanks

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-19 : 10:37:22

Hi scelamko,

It's much easier for us to understand what you need if you give an example (with data structure, sample data, and the desired results), rather than a solution which doesn't quite work.

If I understand what you need, then this trick might help...

--data
declare @t table (id int identity(1, 1), INUM varchar(10), IGRP varchar(10))
insert @t (INUM)
          select '3A'
union all select '3A'
union all select '3010'
union all select '3010'
union all select '1'
union all select '2'
union all select '2'
union all select '2'
union all select '3'

--calculation
declare @previousINUM varchar(10)
declare @i int
set @i = 510000

update @t set @i = case when INUM = @previousINUM then @i else @i + 1 end, IGRP = @i, @previousINUM = INUM

--results
select * from @t

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources