March 6, 2009 at 11:29 pm
Hi
Guys can anybody help me update the rank column as increment of 1
for every different office for same zipcode, one zipcode has many offices and they need to be ranked as 1,2,3 and can be based on datevalue
Is it possible to update through innner join
CREATE TABLE #Table
(
ID INT IDENTITY(1,1),
DateValue DATETIME,
ZipCode nvarCHAR(5),
officecode nvarchar(5),
Rank INT,
CONSTRAINT PK_YourTable_ID_1 PRIMARY KEY CLUSTERED (ID)
)
INSERT INTO #Table
(DateValue,ZipCode,officecode,rank)
SELECT '2008-10-17 00:01:00.000','23001','123','1' UNION ALL
SELECT '2008-10-17 00:02:00.000','23001','133','1' UNION ALL
SELECT '2008-10-17 00:03:00.000','23001','114','1' UNION ALL
SELECT '2008-10-17 00:04:00.000','23001','111','1'UNION ALL
SELECT '2008-10-17 00:05:00.000','23001','112','1'UNION ALL
SELECT '2008-10-17 00:06:00.000','23002','110','1'UNION ALL
SELECT '2008-10-17 00:07:00.000','23002','109','1'UNION ALL
SELECT '2008-10-17 00:08:00.000','23002','108','1'UNION ALL
SELECT '2008-10-17 00:09:00.000','23003','107','1' UNION ALL
SELECT '2008-10-17 00:10:00.000','23003','106','1' UNION ALL
SELECT '2008-10-17 00:11:00.000','23003','105','1' UNION ALL
SELECT '2008-10-17 00:12:00.000','23003','104','1' UNION ALL
SELECT '2008-10-17 00:13:00.000','23004','103','1' UNION ALL
SELECT '2008-10-17 00:14:00.000','23004','102','1' UNION ALL
SELECT '2008-10-17 00:15:00.000','23004','101','1' UNION ALL
SELECT '2008-10-17 00:16:00.000','23004','100','1'
GO
Thanks
simon
March 7, 2009 at 4:38 am
Hello Simon
If you want to create incremental values in an existing table the fastest (and most simple 😉 ) way would be:
[font="Courier New"]
DECLARE @rank INT
SET @rank = 0
UPDATE #Table SET @rank = Rank = @rank + 1
SELECT * FROM #Table
[/font]
Greets
Flo
March 7, 2009 at 9:19 am
Hi
Flo
Thanks for response
if you read the requirment in detail, you will see its not so simple as incremental increasing the rank
field. It is increasing the rank to the offices that lies in the same zipcode and so on for another zipcode.
The expected result will be
Datevalue Zipcode office rank
'2008-10-17 00:01:00.000','23001','123','1' UNION ALL
'2008-10-17 00:02:00.000','23001','133','2' UNION ALL
'2008-10-17 00:03:00.000','23001','114','3' UNION ALL
'2008-10-17 00:04:00.000','23001','111','4'UNION ALL
'2008-10-17 00:05:00.000','23001','112','5'UNION ALL
'2008-10-17 00:06:00.000','23002','110','1'UNION ALL
'2008-10-17 00:07:00.000','23002','109','2'UNION ALL
'2008-10-17 00:08:00.000','23002','108','3'UNION ALL
'2008-10-17 00:09:00.000','23003','107','1' UNION ALL
'2008-10-17 00:10:00.000','23003','106','2' UNION ALL
SELECT '2008-10-17 00:11:00.000','23003','105','3' UNION ALL
March 7, 2009 at 9:32 am
Try this..
;WITH cteTable AS
(
SELECT ROW_NUMBER() OVER ( PARTITION BY officecode ORDER BY datevalue ) AS RowNumber, *
FROM#Table
)
UPDATET
SET Rank = RowNumber
FROM #Table T
INNER JOIN cteTable CT
ON T.ID = CT.ID
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 7, 2009 at 9:37 am
Sorry Try this one ..
;WITH cteTable AS
(
SELECT ROW_NUMBER() OVER ( PARTITION BY officecode ORDER BY datevalue ) AS RowNumber, *
FROM #Table
)
UPDATE T
SET Rank = RowNumber
FROM #Table T
INNER JOIN cteTable CT
ON T.ID = CT.ID
Regards,
Kingston
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 7, 2009 at 9:59 am
Hi
King
I tried it but it did not update the rank at all!
Thanks
Suman
Here is the whole thing you can run along with your update statment
Drop table #Table
CREATE TABLE #Table
(
ID INT IDENTITY(1,1),
DateValue DATETIME,
ZipCode nvarCHAR(5),
officecode nvarchar(5),
Rank INT,
CONSTRAINT PK_YourTable_ID_1 PRIMARY KEY CLUSTERED (ID)
)
INSERT INTO #Table
(DateValue,ZipCode,officecode,rank)
SELECT '2007-10-17 00:01:00.000','23001','123','1' UNION ALL
SELECT '2007-10-17 00:02:00.000','23001','133','1' UNION ALL
SELECT '2007-10-17 00:03:00.000','23001','114','1' UNION ALL
SELECT '2007-10-17 00:04:00.000','23001','111','1'UNION ALL
SELECT '2007-10-17 00:05:00.000','23001','112','1'UNION ALL
SELECT '2007-10-17 00:06:00.000','23002','110','1'UNION ALL
SELECT '2007-10-17 00:07:00.000','23002','109','1'UNION ALL
SELECT '2007-10-17 00:08:00.000','23002','108','1'UNION ALL
SELECT '2007-10-17 00:09:00.000','23003','107','1' UNION ALL
SELECT '2007-10-17 00:10:00.000','23003','106','1' UNION ALL
SELECT '2007-10-17 00:11:00.000','23003','105','1' UNION ALL
SELECT '2007-10-17 00:12:00.000','23003','104','1' UNION ALL
SELECT '2007-10-17 00:13:00.000','23004','103','1' UNION ALL
SELECT '2007-10-17 00:14:00.000','23004','102','1' UNION ALL
SELECT '2007-10-17 00:15:00.000','23004','101','1' UNION ALL
SELECT '2007-10-17 00:16:00.000','23004','100','1'
GO
;WITH cteTable AS
(
SELECT ROW_NUMBER() OVER ( PARTITION BY officecode ORDER BY datevalue ) AS RowNumber, *
FROM #Table
)
UPDATE T
SET Rank = RowNumber
FROM #Table T
INNER JOIN cteTable CT
ON T.Zipcode = CT.Zipcode
select * from #Table
March 7, 2009 at 10:52 am
Sorry Try this one ..
Try this. I hope this works..
;WITH cteTable AS
(
SELECT ROW_NUMBER() OVER ( PARTITION BY zipcode ORDER BY datevalue ) AS RowNumber, *
FROM #Table
)
UPDATE T
SET Rank = RowNumber
FROM #Table T
INNER JOIN cteTable CT
ON T.ID = CT.ID
Regards,
Kingston
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 7, 2009 at 11:34 am
Hi
King
Awesome, it worked perfectly as i expected, thanks a lot
I appreciate all your work and hope to continue this thread if needed:D
Thanks
Simon
March 7, 2009 at 10:46 pm
Glad to help you out.
Regards,
Kingston
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 2, 2009 at 2:35 am
Waw its working perfactly currect.
thanx a lot
April 2, 2009 at 2:49 am
Kingston (3/7/2009)
Sorry Try this one ..Try this. I hope this works..
;WITH cteTable AS
(
SELECT ROW_NUMBER() OVER ( PARTITION BY zipcode ORDER BY datevalue ) AS RowNumber, *
FROM #Table
)
UPDATE T
SET Rank = RowNumber
FROM #Table T
INNER JOIN cteTable CT
ON T.ID = CT.ID
Regards,
Kingston
You don't really need to join back CTE table to main table, you can simply update the CTE table to reflect it to the main table. For e.g.
;WITH cteTable AS
(
SELECT ROW_NUMBER() OVER ( PARTITION BY zipcode ORDER BY datevalue ) AS RowNumber, *
FROM #Table
)
UPDATE cteTable
SET [Rank] = RowNumber
--Ramesh
April 6, 2009 at 12:32 pm
Well. I never thought it can be done this way. Thanks for the additional info Ramesh.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 7, 2009 at 12:10 am
You are welcome!!!
--Ramesh
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply