Converting Arbitrary IDs to 0-Based IDs in SQL

I’m not even sure how to title this blog entry. The problem I had was to extract unique IDs from a SQL table and then convert the IDs into 0-based IDs. There are really two issues. First, how to extract unique values from a table, and second, how to convert arbitrary IDs into 0-based IDs. This leads to a sub-problem of how to update a SQL table with row numbers. For example if I have a table like this:
fromNode  toNode
111       222
111       333
111       444
222       333
222       555
333       555
333       666
444       555
I want to create a lookup table like this:
nodeID    value
111       0
222       1
333       2
444       3
555       4
666       5
The approach I took was to create a result table:
create table #tblNodeValues
nodeid bigint not null,
value int not null
Then I extracted the unique IDs, but put a -1 placeholder value in the 0-based ID column:
insert into #tblNodeValues(nodeid, value)
(select distinct fromNode, -1 from tblFromTo)
union (select distinct toNode, -1 from tblFromTo)
Then I used a derived table in combination with the ROW_NUMBER() function to update the temporary 0-based IDs with their correct values:
update derivedTbl
set value = rownum
select value, (ROW_NUMBER() over (order by nodeid)) -1 as rownum from #tblNodeValues
as derivedTbl
The -1 here is to make the IDs 0-based because the ROW_NUMBER() function is 1-based. This was quite an interesting problem and I relied almost entirely on helpful posts I found on the Internet.

This entry was posted in Software Test Automation. Bookmark the permalink.