Helpful Tip On Re-alphabetizing Categories

I still like leaving the Display Order field in charge of determining category order (rather than alphabetizing it in code) but I like refreshing my categories from time to time as new ones are added.

This is a quick SQL statement that renumbers certain category families if it's helpful to anyone:

WITH OrderedCategories AS
(
  SELECT CategoryID, Name, ROW_NUMBER() OVER (ORDER BY Name) AS 'RowNumber' FROM Nop_Category WHERE ParentCategoryID=29
)
UPDATE Nop_Category SET DisplayOrder=OC.RowNumber FROM OrderedCategories OC WHERE OC.CategoryID=nop_Category.CategoryID

By using the ROW_NUMBER method within a partition of a result set, it makes fast work of putting the automatically generated DisplayOrder number after the result partition is queried out.

I hope that helps someone!

http://blog.NielsenData.com
The FUZION Agency

Brilliant. This works really well.

I've updated the query to work with 2.65

Note:  I also multiplied the ROW_NUMBER() * 10 so that each DisplayOrder is spaced out by intervals of 10.  This allows me to add more categories and not have to renumber them so often.


WITH OrderedCategories AS
(
  SELECT ID, Name, DisplayOrder, ROW_NUMBER() OVER (ORDER BY Name) * 10 AS 'RowNumber' FROM Category  WHERE ParentCategoryID=2
)
UPDATE Category SET DisplayOrder=OC.RowNumber FROM OrderedCategories OC WHERE OC.ID=Category.ID


-PwH

This modified version updates all categories and reorders them by name within their parent category using the PARTITION BY clause.

    WITH OrderedCategories AS
    (
        SELECT Id, Name, DisplayOrder, ROW_NUMBER() OVER (PARTITION BY ParentCategoryId ORDER BY Name) * 10 AS RowNumber FROM Category
    )
    UPDATE c SET DisplayOrder = oc.RowNumber
    FROM Category c
    JOIN OrderedCategories oc ON c.Id = oc.Id