Increasing value based on criteria in a single database query

Question

I have a table that has a column labeled "sortorder", which is used to allow the client to manually reorder each item. The table also has a column called "CategoryId".

I was curious if I were to bulk import a dataset in which I knew all the data, including CategoryId, how could I specify a sort value for SortOrder inside the query so that it went from 1 to X within each unique CategoryId.

Thanks to everyone.

Answer 1

I'm not sure I understand your question, but I think you are asking how to synthesize a suitable SortOrder during insertion into a table. You must use ROW_NUMBER () categorized. Of course, you will need to define a sorting criterion that gives an order of magnitude from 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

It looks like you need 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

, , SQL Server 2000, .

. , sortorder, 1 .

, -

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

prod.