>Is there a way to control the sort order of the child records in a relationship or a SQL SELECT statement? This seems rather basic but not very easy unless I'm missing something. Say you have a CUSTOMERS table and an ORDERS table. You do a SQL SELECT and you want the orders to appear by date. The orders table must be indexed on customer ID for the relation to work which seems to prevent the date sort. Any attempt to order by date breaks the relation.
>
>There must be a way to do this.
>Help please.
You can specify more than one field in the ORDER BY clause.
For instance:
select customers.fullname, orders.date, orders.totalamount;
from customers join orders on customers.cust_id = orders.cust_id;
order by date, fullname;
into cursor temp
You do NOT need an index for this to work (although the existence of indices will affect the speed, making the query often faster, sometimes slower).
The table doesn't even need to be open before you run the SELECT - SQL statement.
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)