Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
micnie_2020
Posting Yak Master
232 Posts |
Posted - 2012-11-29 : 18:47:03
|
Hi All,I have a set of data eg:-Before:ID Desc Datt GAMt1 GAMt2 ...1 xxx 01/12 1000 10001 xxx 02/12 1000 10002 bb 01/12 500 5002 bb 02/12 500 500 2 bb 03/12 500 500 After( I need this output, mean 1 ID only have 1 GAmt1 & GMAt2):-ID Desc Datt GAMt1 GAMt2 .....1 xxx 01/12 1000 10001 xxx 02/12 0 02 bb 01/12 500 500 2 bb 02/12 0 02 bb 03/12 0 0 Please advise.Thank you.Regards,Micheale |
|
Elizabeth B. Darcy
Starting Member
39 Posts |
Posted - 2012-11-29 : 19:29:52
|
This is one way. There are perhaps other ways that utilize the MIN function:;WITH cte AS( SELECT GAMt1, GAMt2, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Datt) AS RN FROM YourTable)UPDATE cte SET GAMt1 = 0, GAMt2 = 0WHERE RN > 1; ________________________________________-- Yes, I am indeed a fictional character. |
|
|
|
|
|