Feature in SQL Server to Update Field with Sort Numbers

Without having to open a cursor and update one record at a time, is there a feature in SQL Server 2012 that I can use to assign a sort number to each Qty for the same Id as follows:
Id         Qty        SortNo
1           20          1
1           50          2
1           100        3
2           10          1
2           70          2

Note: The table would start out with blank SortNo data and the code of the stored procedure would update the SortNo of each record.
Who is Participating?
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.

Olaf DoschkeSoftware DeveloperCommented:
In the simplest case don't store it at all, such a sorting and numbering can be achieved on the fly in a query with OVER clause:

Declare @test as Table (Id int, Qty  int)

Insert Into @test Values (1,50),(1,20),(1,100),(2,70),(2,10)

Select *, ROW_NUMBER() OVER (Partition By Id Order By Qty) as SortNo
From @test 
Order By Id, Qty

Open in new window

Partiton means reset of row numbering to 1, Partition by Id means reset to 1 for each Id value, and the order determines order of numbering, of course.

The whole concept is working very optimized. Let me go out on a limb and state this is computed faster than reading it from a stored SortNo column. The only advantage in a stored sortno is, you may override the automatic sorting, you may use it for a case like comparing last week/month/quarter/year sorting with current computed sorting and more.

So if you like, you may take the result as basis of populating a SortNo column.

Bye, Olaf.

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
awking00Information Technology SpecialistCommented:
If you really need to have the sortno column updated, you can combine Olaf Doschke's query with a merge operation -
merge into yourtable t
(select id, qty, row_number() over (partition by id order by qty) as sortno
 from yourtable) x
on (t.id = x.id and t.qty = x.qty)
when matched then
update set
s.tortno = x.sortno;
Declan_BasileITAuthor Commented:
Thank you both.  I ended up using this Statement (Note: the actual table is a bit more complicated than the one I used to demonstrate the question)

      (SELECT T.PK1Id, T.Float1, ROW_NUMBER() OVER (Partition By PK1Id Order By Float1) As SortNo
      FROM TableB T WHERE T.ProgramSessionId = @ProgramSessionId) AS Q ON B.PK1Id = Q.PK1Id AND B.Float1 = Q.Float1
      WHERE B.ProgramSessionId = @ProgramSessionId;
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

From novice to tech pro — start learning today.