Auto Generate Sort Orders with SQL UPDATE

Question

I recently imported about 60k records into a table that relates data in one table to data in another table. However, my client has since requested that a sort order be added to all 60k records. My hope is there is a nice clean way to auto generate these sort orders in a SQL Update. The finished data should look something like this:

item1ID  item2ID  sortOrder
1           123       1
1           12        2
1           45        3
1           22        4
1           456       5
2           5         1
2           234       2
2           56        3

Can this be done? Any suggestions would be hugely appreciated.

--Anne

Answer 1

You could use ROW_NUMBER and partition by Item1ID

UPDATE t1
SET t1.SortOrder = t2.SortOrder 
FROM @t t1
INNER JOIN
(SELECT Item1ID, Item2ID, ROW_NUMBER() OVER
    (PARTITION BY Item1ID ORDER BY Item1ID, Item2ID) AS SortOrder
from @t) t2
ON t1.Item1ID = t1.Item1ID 
AND t1.Item2ID = t2.Item2ID

Answer 2

Theoretically, you could say:

update mytable 
  set sortOrder = row_number() 
     over (partition by item1id order by item1id, item2id) from mytable

However, that'll give you an error message:

Msg 4108, Level 15, State 1, Line 1
Windowed functions can only appear in the SELECT or ORDER BY clauses.

So you actually have to do it in two steps - first select the values into a temp table, and then update your original from the temp table.

For example:

select 
    item1ID, 
    item2ID, 
    row_number() 
        over (partition by item1id order by item1id, item2id) as sortOrder 
  into #tmp 
  from mytable

update mytable 
  set sortOrder = T.sortOrder 
  FROM 
    mytable M 
    inner join #tmp T 
        on M.item1ID = T.item1ID 
        AND M.item2ID = T.item2ID

drop table #tmp

Answer 3

You're touching on something fundamental about the relational model here. In databases on the whole, there's no such thing as an intrinsic ordering. If you want to get an ordering out of data whenever you look at a table, you must specify that order explicitly.

So in a general sense, you're asking for the impossible. You can't just UPDATE a table and get an automatic ordering out any query you make on it. But in a query-by-query sense, you could always put "ORDER BY item1ID, sortOrder" in any SELECT statement you apply to the table.

In SQL Server 2005, you could write a view and present it to your client, using this old hack:

SELECT TOP 100 PERCENT
    item1ID, item2ID, sortOrder  -- and all the other columns
FROM YourTable
ORDER BY item1ID, sortOrder;

There are ways of making such a view updateable, but you'll need to research that on your own. It's not too hard to do.

If you're never going to insert or change data in this table, and if you're willing to reimport the data into a table again, you could define your table with an identity, then insert your data into the table in the appropriate order. Then you would always order by the one identity column. That would work if your client always views the data in a program that allows sorting by a single column. (BTW, never use the IDENTITY function for this purpose. It won't work.)

CREATE TABLE YourTable (
    SingleSortColumn  INT IDENTITY(1,1) NOT NULL,
    ...
);
INSERT INTO YourTable (
    item1ID, item2ID, sortOrder  -- everything except the SingleSortColumn
)
SELECT  -- all your columns
INTO YourTable
FROM yadda yadda yadda
ORDER BY item1ID, sortOrder;

Hope that's helpful. Sorry if I'm being pedantic.