Re: more on SELECT ordering

> 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: > > 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? Just about! You are basically defining a tree of records, linked by the ref_num foreign key column. You want to do a depth-first search (i.e. follow down 10, 11, 12, 13 before doing the link to 11), I think, with the added complication that the output tree nodes must be sorted. Your best bet is to develop some cast iron rules for how to sort the records (try drawing this example as a tree and work out which branches you need); then you have a number of options: - add a 'sort_order' column, which is pre-computed at insert or update time to have a completely artificial number that represents where in the output sort order th record should come. This involves a fair bit of work, and updates may even involve a ripple down effect, but will allow you to use the Informix 'order by' - write your own tree walking and sort routine, working on records loaded into memory. This will be a lot of work and may end up running into memory problems. This is an example of the 'recursive query' problem; computer scientists are still trying to find an elegant way of doing this in RDBMSs. > Regards, Ben > -- > The Direct Connection sys0001@dircon.co.uk ...!ukc!dircon!sys0001 > =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Richard Donkin Hoskyns Open Systems Division Internet: richardd@inset.co.uk 190 City Road, LONDON EC1V 2QH UUCP: ...!mcsun!ukc!inset!richardd United Kingdom Fax: +44 71 251 2853 Tel: +44 71 251 2128