Author |
Topic |
Brittney10
Posting Yak Master
154 Posts |
Posted - 2012-12-28 : 11:43:29
|
Below is an example record set:ID_____Date________Value____Assoc123____12/28/2012____0_______1123____12/27/2012____0_______1123____12/26/2012____0_______2123____12/25/2012____0_______2What I need to be able to do is update [Value] to 2 when [Date] is the most recent per [Assoc]. So in the above query, record #1 and record #3 would both be updated. (Grouping by ID and Assoc)Desired Result set: ID_____Date________Value____Assoc123____12/28/2012____2_______1123____12/27/2012____0_______1123____12/26/2012____2_______2123____12/25/2012____0_______2Thanks for the help ahead of time. |
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-28 : 11:54:53
|
[code]Update TSet T.Value = Isnull(2,0)from(Select *,DENSE_RANK() OVER(PARTITION BY ID,Assoc Order by Date desc) as Seqfrom Table)TWhere T.Seq = 1[/code] |
|
|
Brittney10
Posting Yak Master
154 Posts |
Posted - 2012-12-28 : 12:33:12
|
That works great! Thank you. But now I have another problem. I have duplicate records, so I need to update the one who has the most recent identity value. How do I add that logic? |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-28 : 12:47:22
|
[code]Update TSet T.Value = 2from(Select *,DENSE_RANK() OVER(PARTITION BY ID Order by Date desc) as Seqfrom Table)TWhere T.Seq = 1[/code] |
|
|
Brittney10
Posting Yak Master
154 Posts |
Posted - 2012-12-28 : 15:01:00
|
That doesn't quite work because I still need to partition by ID and Assoc.quote: Originally posted by sodeep
Update TSet T.Value = 2from(Select *,DENSE_RANK() OVER(PARTITION BY ID Order by Date desc) as Seqfrom Table)TWhere T.Seq = 1
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-28 : 15:18:46
|
Do you have Primary key with identity? or explain what you mean by this "I need to update the one who has the most recent identity value" |
|
|
Brittney10
Posting Yak Master
154 Posts |
Posted - 2012-12-28 : 16:08:25
|
Identity____ID_____Date________Value____Assoc1___________123____12/28/2012____0_______12___________123____12/28/2012____0_______13___________123____12/27/2012____0_______24___________123____12/27/2012____0_______2Since I have duplicate records, I need to update the record with the most recent Identity (auto incrementing) column. So record #2 and #4 would be updated. The code you gave me works perfect, except [Value] can only be = 2 once per ID and Assoc. I hope that makes more sense. Thanks for the help! |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-28 : 16:18:52
|
Ok then do thisUpdate TSet T.Value = 2from(Select *,DENSE_RANK() OVER(PARTITION BY ID Order by Date desc,<Your Identity Column> desc) as Seqfrom Table)TWhere T.Seq = 1 |
|
|
Brittney10
Posting Yak Master
154 Posts |
Posted - 2012-12-28 : 16:20:56
|
Perfect! I swear I tried that! Perhaps I was doing "Order By Date, Identity desc". Thank you so much for the help!quote: Originally posted by sodeep Ok then do thisUpdate TSet T.Value = 2from(Select *,DENSE_RANK() OVER(PARTITION BY ID Order by Date desc,<Your Identity Column> desc) as Seqfrom Table)TWhere T.Seq = 1
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-28 : 16:21:43
|
NP. Atleast you learnt now :) |
|
|
Brittney10
Posting Yak Master
154 Posts |
Posted - 2012-12-28 : 16:39:22
|
I did learn a lot! Thanks for that!quote: Originally posted by sodeep NP. Atleast you learnt now :)
|
|
|
|