more on SELECT ordering

Several people replied to my message about ordering of a series of records, some of which refer to each other. Here's the list of transaction records again: trans_num ref_num 1 2 3 4 2 5 6 7 2 8 9 1 The general opinion was that I should give every record a ref_num, and in the cases where the ref_num did not need to refer back to a previous transaction, make the ref_num equal to the trans_num. Then, if I ORDER BY ref_num, trans_num the transactions would appear in the right order. I thought this was correct, until I realised that potentially my application could get more complicated. The problem is that each of the "sub transactions" could also be a "parent" to some "sub transactions" itself, and each of those "sub transactions" could alsobe a parent. For example, in the table above, if you add the following records: 10 7 11 7 12 10 13 12 The required output ordering would be: trans_num ref_num 1 1 9 1 2 2 4 2 7 2 10 7 <- this refers to trans_num 7, so must come after 7 12 10 <- this refers to 10, so must come after 10 13 12 <- this refers to 12, so must come after 12 11 7 <- see note below 3 3 5 5 6 6 8 8 trans_num 11 has to come after 13, because the link 10 to 12 to 13 should have a greater precedence than 7 to 11. Similarly, say another record was added: 14 12 This would go in the list between trans_nums 12 and 13. Does this make sense? The SELECT statement for this probably gets alot more complicated. Any ideas as to the best way to achieve this ordering would be gratefully received! Thank you for your help. Regards, Ben -- The Direct Connection sys0001@dircon.co.uk ...!ukc!dircon!sys0001