• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 456
  • Last Modified:

update sequence field in Sql

So i have a table with lets say four fields on table1

Date
Group
Status
Sequence_number

i need to update the sequence grouping the fields by the other three fields.

so if there are 4 records where Date, Group and Status are the same,  the Sequence_number would start at 101, 102, 103, 104 then start again at 101 for the next group of records where these three fields are the same.

thanks in advance
0
damixa
Asked:
damixa
  • 3
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Here's the SELECT, with some supporting testing code.  Copy-paste this into your SSMS, verify it works, then we'll have to figure out how to UPDATE with the final set.

CREATE TABLE #tmp (date datetime, the_group varchar(10), status varchar(10), sequence_number int) 

INSERT INTO #tmp (date, the_group, status)
VALUES 
	('1-1-2012', 'Group1', 'Ready'), 
	('1-1-2012', 'Group1', 'Ready'), 
	('1-1-2012', 'Group1', 'Ready'), 
	('1-1-2012', 'Group1', 'Not Ready'), 	
	('1-1-2012', 'Group2', 'Ready'), 
	('1-1-2012', 'Group2', 'Not Ready'), 
	('1-1-2012', 'Group2', 'Not Ready'), 
	('7-1-2012', 'Group1', 'Ready'), 
	('7-1-2012', 'Group1', 'Ready'), 
	('7-1-2012', 'Group1', 'Ready'), 
	('7-1-2012', 'Group1', 'Not Ready'), 	
	('7-1-2012', 'Group2', 'Ready'), 
	('7-1-2012', 'Group2', 'Not Ready'), 
	('7-1-2012', 'Group2', 'Not Ready')
	
SELECT * FROM #tmp

SELECT date, the_group, status, sequence_number = ROW_NUMBER() OVER (PARTITION BY date, the_group, status ORDER BY date ) + 100
FROM #tmp
ORDER BY date, the_group, status

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
If there's a PK on your table, then this works...

DROP TABLE #tmp

CREATE TABLE #tmp (id int identity(1,1), date datetime, the_group varchar(10), status varchar(10), sequence_number int) 

INSERT INTO #tmp (date, the_group, status)
VALUES 
	('1-1-2012', 'Group1', 'Ready'), 
	('1-1-2012', 'Group1', 'Ready'), 
	('1-1-2012', 'Group1', 'Ready'), 
	('1-1-2012', 'Group1', 'Not Ready'), 	
	('1-1-2012', 'Group2', 'Ready'), 
	('1-1-2012', 'Group2', 'Not Ready'), 
	('1-1-2012', 'Group2', 'Not Ready'), 
	('7-1-2012', 'Group1', 'Ready'), 
	('7-1-2012', 'Group1', 'Ready'), 
	('7-1-2012', 'Group1', 'Ready'), 
	('7-1-2012', 'Group1', 'Not Ready'), 	
	('7-1-2012', 'Group2', 'Ready'), 
	('7-1-2012', 'Group2', 'Not Ready'), 
	('7-1-2012', 'Group2', 'Not Ready')
	
SELECT * FROM #tmp ORDER BY id

UPDATE #tmp
SET sequence_number = rn.seq
FROM #tmp
  JOIN (
	SELECT id, ROW_NUMBER() OVER (PARTITION BY date, the_group, status ORDER BY date ) + 100 as seq
	FROM #tmp) rn ON #tmp.id = rn.id

SELECT * FROM #tmp ORDER BY id




 

Open in new window

0
 
damixaAuthor Commented:
perfect, thanks
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now