CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Nate222
Nate222

Reputation: 886

Numbering Column Records in Ordered Table

Table Schema:

StatusId    Status  DisplayColor    IsOpen  DivisionId  SortOrder
4           Open    3e696c          1       34          1
5           Closed  3e696c          0       34          2
1           Open    3e696c          1       35          1
2           Closed  3e696c          0       35          2

The SQL Query I have to order the columns the way I need to number them:

select  StatusId, Status, DisplayColor, IsOpen, DivisionId, SortOrder
from    TV_Statuses s
order by DivisionId, IsOpen desc

I'm trying to create a script that can go through and assign SortOrder incrementally by DivisionId and then IsOpen. So here, STatusId of 4 would have a SortOrder of 1. StatusId of 5 would have SortOrder 2. The same for the bottom 2 rows, but here a division could have 100 rows, 70 open and 30 closed so the open would be numbered 1 - 70 and the Closed would be 71 - 100.

Any advice on a way to do this without resorting to cursors? My spidey sense tells me there's probably a more straightforward way to do it.

Any assistance is appreciated.

EDIT: OK, so actually in the database now, the SortOrder column only has NULLs in it. I want to update the SortOrder column so that each Division (using DivisionId) has an incrementing number in SortOrder. For instance above DivisioniId of 34 has to rows with one IsOPen = 1 and one IsOpen = 0. So I would want to order those two rows with SortOrder 1 and 2 respectively. But a division could have 10 rows. Then that divisions SortOrder fields would be 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. It's hard to explain but hopefully that helps a little.

Upvotes: 0

Views: 88

Answers (1)

anon
anon

Reputation:

Since SQL Server 2005 you can apply windowing functions like ROW_NUMBER() to a set (or a subset, often called a partition but not to be confused with table partitioning). I believe what you are looking for is the following:

SELECT 
  StatusId, Status, DisplayColor, IsOpen, DivisionId, 
  SortOrder = ROW_NUMBER() OVER 
    (PARTITION BY DivisionId ORDER BY IsOpen DESC, StatusId)
FROM dbo.TV_Statuses AS s
ORDER BY DivisionId, IsOpen DESC;

Upvotes: 2

Related Questions