i have a table with list of unsorted items.
I need to sort it ASC
by Stext
column and UPDATE `OrderNo" starting 1
If there are 4 rows, last OrderNo should be 4, if there is more then 5, etc...
SELECT [ID]
,[RefAg]
,[IDS]
,[SText]
,[OrderNo]
FROM [Stw].[dbo].[List]
where refAg = 22
order by Stext ASC;
table sorted by previous query:
ID |RefAg | IDS | SText | OrderNo
----|-------|--------------------|---------------------------|--------
1001| 22 | Item A | Item A | 2
1128| 22 | Item B | Item B | 4
986 | 22 | Item C | Item C | 1
1122| 22 | Item D | Item D | 3
need to update OrderNo
like this:
ID |RefAg | IDS | SText | OrderNo
----|-------|--------------------|---------------------------|--------
1001| 22 | Item A | Item A | 1
1128| 22 | Item B | Item B | 2
986 | 22 | Item C | Item C | 3
1122| 22 | Item D | Item D | 4