sql for

[goransaler]

Hello

I have a database with some fields like this


Name Groupnr
aa 1
bb 1
cc 2
dd 2
ee 2
ff 4
sd 4
we 5
ss 7
....

and i like the Groupnr field
to be like this

Name Groupnr
aa 1
bb 1
cc 2
dd 2
ee 2
ff 3
sd 3
we 4
ss 5
....

What i like to do is if Groupnr
miss one number in counting i like to change
next to that and so on..

I would be greatful fo help.

 

[zhavic]

You can create temporary table with IDENTITY column,
than insert rows from your table into that temporary table ordered and grouped by Groupnr and then update original table, like this:

CREATE TABLE #tmp ( 
					NewGroupnr integer IDENTITY( 1, 1 ),
					PreviousGroupnr integer,
				  )

/* this create continuous numbers in NewGroupnr column of #tmp table */
INSERT INTO #tmp ( PreviousGroupnr )
	SELECT Groupnr
		FROM your_table
	GROUP BY Groupnr
	ORDER BY Groupnr

/* update Groupnr with new created numbers */
UPDATE your_table SET Groupnr = NewGroupnr
	FROM your_table
		INNER JOIN #tmp ON #tmp.PreviousGroupnr = your_table.Groupnr

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[goransaler]

Thanks i will check this

 

[ESquared]

UPDATE G3 SET
      GroupNo = Cnt
   FROM Groups G3 INNER JOIN
      (SELECT 
         GroupName,
         Cnt = Count(G2.GroupNo)
         FROM Groups G1
         INNER JOIN (SELECT DISTINCT GroupNo FROM Groups) G2
            ON G1.GroupNo >= G2.GroupNo
         GROUP BY GroupName
      ) G4 ON G3.GroupName = G4.GroupName

If GroupName is not a unique column then you need to modify this query to use that unique colum instead.

 

[goransaler]

sorry i was away
thanks
i will check this

 

[goransaler]

Thanks ESquared
But do you use 4 tables???

 

[ESquared]

I used the same table 3 times, and one of the 'tables' is a derived table.

-------------------------------------
A sacrifice is harder when no one knows you've made it.