SQL Server syntax question

I need to update a column depending on the order of another field. For instance, if columA is the lowest I need to update ColumnB to 1. There are multiple records for each account. So accountA can have 5 rows so I need to update columnB in order ascending from columnA...If this doesnt make sense please let me know. Thanks
healthcheckincAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
If you are indeed using SQL Server 2005, you should be able to do this as follows:

;WITH accountOrdered AS (
   SELECT *,
   row_number() OVER (PARTITION BY accountA ORDER BY columnA) AS rNum
   FROM TableName
)
UPDATE accountOrdered
SET columnB = rNum

You can just run the SELECT statement first to see if this is what you meant and then since common table expressions are virtual tables the update should work fine.
0
healthcheckincAuthor Commented:
ok I think we are on the same page. I may need a little more clarification though. My table is Transactions and the accountid can have multiple rows and I need to set the position from the revcode ascending. So my query is Select accountid, position, revcode from transactions order by accountid, revcode. Now, I need to update position where the revcode is smallest to greatest. So if I get 3 rows for an accountid I need to update  position for each accountid where the revcodes are in order asc..First recrod would be set position = 1, the next record for the same accountis would be set position = 2 and so on.....Does this make sense?
0
Kevin CrossChief Technology OfficerCommented:
Makes perfect sense.  This may work for you as the PARTITION BY means to group on accountid and then ORDER BY revcode so your row_number() will be the order of each revcode ASC for each accountid.

;WITH transOrdered AS (
   SELECT accountid, position, revcode
   , row_number() OVER (PARTITION BY accountid ORDER BY revcode) AS rNum
   FROM Transactions
)
UPDATE transOrdered
SET position = rNum
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

healthcheckincAuthor Commented:
I dont know who you are but you are my new hero. That was very impressive. Well done...
0
healthcheckincAuthor Commented:
awesome
0
Kevin CrossChief Technology OfficerCommented:
Thanks and just glad to help.

Respectfully yours,
Kevin
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.