Re: Getting a specific SELECT order

Ben writes in X-Informix-List-Id: <list.362> >I'm trying to put together an SQL statement which will select a >particular set of transactions in a particular order. > etc >The two important fields are the trans_num and ref_num. >Each transaction has a unique trans_num. >The ref_num is used to group together related transactions. >It does this by being set as equal to a particular trans_num (sort of >like a parent transaction). > etc >Basically, I want all transactions with the ref_num the same as a >particular >trans_num, to be grouped together. However, if no ref_num exists, the >transactions should be ordered just by their trans_num. >Can anyone suggest an efficient SQL SELECT statement which will >produce this ordering? I don't know whether this is any use to you, it will depend on the design of the rest of the system, but the simplest and most efficient solution is to set ref_num = trans_num for all primary transactions and ref_num = primary trans_num for all secondary transactions. Then with an index raised on ref_num, trans_num and select ... order by ref_num, trans_num you will get what you want fetched directly from the database via index. This of course requires ref_num to be filled every time and for programs that check ref_num to also check when it is = to its own trans_num where you now check = null. Hope this helps. Cheers, Jim -------------------------------------------------------------------- Name: Jim Gordon Internet: jgordon@ssf-sys.DHL.COM Company: DHL Systems Inc Phone: (415) 358-5911 Address: 1700 S. Amphlett Blvd. Fax: (415) 571-6429 San Mateo, CA 94402 --------------------------------------------------------------------