I have inherited a db, and I need to transform an existing parent-child relationship into all parents.

Let me first explain the existing data.The tables have a structure something like:

Thing                  Widget
------------           ------------
thing_id               widget_id
thing_name             widget_name
thing_rank             widget_rank

The data is presented to the end user as a list of Thingss, and occasionally a Thing has-a Widget, and the widget is presented as being "beneath" or "sub" to the Thing. So there's some hierarchy. The other key thing is that this presentation is sorted by the thing_rank and widget_rank.

Here's a concrete example with data:


1001  "thing blue" 1
1002  "thing green" 3
1003  "thing red" 4
1004  "thing yellow" 2


2001  "widget cucumber" 2  1002
2002  "widget bean" 1  1002

This would be presented with the Things ordered by rank, then the two child Widgets belong to "thing green", and ordered by their rank:

1 thing blue
2 thing yellow
3 thing green
   1 widget bean
   2 widget cucumber
4 thing red

The task is to remove the hierarchy, but preserve the rank order. For example, the above data would be assigned a new rank, and all data would belong to the same relation -- no longer a parent-child relationship. For example:

1 thing blue
2 thing yellow
3 thing green
4 widget bean
5 widget cucumber
6 thing red

And the single relation would be:

stuff_type   (thing|widget)

I'm looking for how to create a Stuff view that presents both Thing and Widget data, with new rank assignments.


Since you have not provided any RDBMS I've tried to post an answer that should work in more than one system.

Records can be ordered by both ranks and numbered using row_number() window function.

select row_number() over (order by r1, r2) id, name
from (
      select t.name as [name], t.[rank] r1, 0 as r2
      from   thing t
      union all
      select w.name as [name], t.[rank] r1, w.[rank] r2 
      from   widget w
      join   thing t
      on     t.id = w.thing_id
     ) x;
id | name           
:- | :--------------
1  | thing blue     
2  | thing yellow   
3  | thing green    
4  | widget bean    
5  | widget cucumber
6  | thing red      

dbfiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.