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
Who is Participating?
Kevin CrossConnect With a Mentor Chief 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
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.
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?
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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

Respectfully yours,
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.