SQL Server Query :Sort Ordering Query

Sorting and re-ordering is one of the most common requirement in today's demanding industry and there are many approaches to achieve the same.

Here I'm sharing one of the easiest back-end solution to over come re-Ordering from database function/Store Procedure.

The advantage of having a database driven ordering would help is saving the processing call from application to database, & can have control over the functionality at ease.

Below shows the ordering after removing few titles.

Current Ordering ---------------------- Title -- Order Link 01-- 1 link 02 -- 2 link 03 -- 4 link 04 -- 6 link 05 -- 7 link 06 -- 8

To have current order in place we can have a database Function create to get corrected Ordering update to table /Column.
STEP 01 : GET CORRECT ORDER.

SQL function :  ROW_NUMBER()

Creating Code Block to get needed Ordering :

SELECT Title,SortOrder,
ROW_NUMBER() OVER (ORDER BY (select 1))
AS NEW_SORTORDER
FROM dbo.tablename

ROW_NUMBER needs to have Over followed ORDER BY attributes


STEP 02 : ORDER & UPDATE WITH TABLE

(a) Create a With Block
(b) Update with join on WITH black

WITH Correct_SortOrder AS
(
SELECT Title,SortOrder,
ROW_NUMBER() OVER (ORDER BY (select 1))AS NEW_SORT_ORDER
FROM dbo.tablename
)
Update
dbo.tablename
SET
dbo.tablename.SortOrder=Correct_SortOrder.NEW_SORT_ORDER
FROM
tablename
INNER JOIN
Correct_SortOrder
ON
tablename.Title=Correct_SortOrder.Title;






Hope this Help you. Happy Coding :)