Incrementing value based on criteria in a single database query

Question

I have a table which has a column labeled 'sortorder' which is used to allow customer to manually change the order of each item. The table also has a column labeled 'CategoryId'.

I was curious, if I was bulk importing a set of data in which I knew all data, including CategoryId, how I could specify the incrimenting value for 'SortOrder' inside the query, so that it went from 1 to X within each unique CategoryId.

Thanks everyone.

Answer 1

I'm not sure I understand your question but I think what you're asking is how to synthesize an appropriate SortOrder during an insert into the table. You should use ROW_NUMBER() with partitioning by CategoryId. Of course you will need to define a sorting criteria that gives the propert order of '1 to X':

INSERT INTO myTable (SortOrder, CategoryId, <other columns> ...)
SELECT ROW_NUMBER() OVER (PARTITION BY CategoryId ORDER BY mySortCriteria)
  , CategoryId
  , <other columns> ...
  FROM SourceTable;

Answer 2

Sounds like you're needing to use the row_number function in your import.

INSERT MyTable(SortOrder, ...)
SELECT  SortOrder = row_number() over (partition by CatgoryID order by SomeOtherField), ...
FROM    MyTable

Answer 3

For anyone who might come along later who is still in SQL Server 2000, here is another way to accomplish the task.

Using a staging table of some sort to fix up the data from the bulk insert. Make sure it has a column called sortorder which is initally populated with a 1 and an identity based id column.

then you can update this with a self join, something like

update t
set sortorder = t1.sortorder +1
from test t
join Test t1 on t.id = t1.id+1

Then put the data into your prod table.