CODEWITHSUNDEEP
sqlsql-server
DanP
DanP

Reputation: 6478

Applying a sort order to existing data using SQL 2008R2

I have some existing data that I need to apply a "SortOrder" to based upon a few factors:

  1. The ordering starts at "1" for any given Owner
  2. The ordering is applied alphabetically (basically following an ORDER BY Name) to increase the sort order.
  3. Should two items have the same name (as I've illustrated in my data set), we can apply the lower sort order value to the item with the lower id.

Here is some sample data to help illustrate what I'm talking about:

What I have:

Id      OwnerId   Name                     SortOrder  
------  -------   ----------------------   ---------  
1        1         A Name                   NULL                        
2        1         C Name                   NULL  
3        1         B Name                   NULL  
4        2         Z Name                   NULL  
5        2         Z Name                   NULL  
6        2         A Name                   NULL

What I need:

Id      OwnerId   Name                     SortOrder  
------  -------   ----------------------   ---------  
1        1         A Name                   1                        
3        1         B Name                   2  
2        1         C Name                   3  
6        2         A Name                   1  
4        2         Z Name                   2   
5        2         Z Name                   3

This could either be done in the form of an UPDATE statement or doing an INSERT INTO (...) SELECT FROM (...) if it's easier to move the data from one table to the next.

Upvotes: 2

Views: 1189

Answers (1)

marc_s
marc_s

Reputation: 755531

Easy - use a CTE (Common Table Expression) and the ROW_NUMBER() ranking function:

;WITH OrderedData AS
(
   SELECT Id, OwnerId, Name, 
      ROW_NUMBER() OVER(PARTITION BY OwnerId ORDER BY Name, Id) AS 'SortOrder'
   FROM
      dbo.YourTable
)
SELECT *
FROM OrderedData
ORDER BY OwnerId, SortOrder

The PARTITION BY clause groups your data into group for each value of OwnerId and the ROW_NUMBER() then starts counting at 1 for each new group of data.

Update: If you want to update your table to set the SortOrder column - try this:

;WITH OrderedData AS
(
   SELECT 
      Id, OwnerId, Name, 
      ROW_NUMBER() OVER(PARTITION BY OwnerId ORDER BY Name, Id) AS 'RowNum'
   FROM
      dbo.YourTable
)
UPDATE OrderedData
SET SortOrder = RowNum

That should set the SortOrder column to the values that the ROW_NUMBER() function returns

Upvotes: 6

Related Questions