[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

update sequence field in Sql

Posted on 2013-06-26
4
Medium Priority
?
453 Views
Last Modified: 2013-06-26
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
Comment
Question by:damixa
  • 3
4 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39278044
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
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 39278106
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
 

Author Closing Comment

by:damixa
ID: 39278853
perfect, thanks
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39279010
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question