<< RBase should include the option of allowing an order by clause when LISTOF is used. >>
I don't mean to be a cranky old man here, but that would be semantically impossible. Consider this: SELECT CustID, LISTOF(InvNo), MAX(InvNo) AS InvNo + FROM Invoices + GROUP BY CustID + ORDER BY InvNo DESC I think everyone clearly understands that the intent is to order the result set by invoice number, descending. How could that ORDER BY also be applied to a completely separate ordering operation that should happen inside the LISTOF? Those are two separate operations. In order to achieve the effect you're looking for, we'd need some separate syntactical element to specify the internal ordering of each aggregated record. We'd have to invent something like (LISTOF(InvNo ORDERED BY InvNo)) or (SORTLIST(LISTOF(InvNo)). Either would be fine, but would have to be added to the language to accomplish the dual goals of sorting the final result set and also the internal result set for each aggregated row. Right now, R:Base already has a way to specify that internal sorting — using an intermediate view. I agree that it's a little more trouble to use than the syntax I invented above, but it has the benefit of already existing. << After all it may not be in primary key order that you want the list returned. >> That is true, which is why you need to specify the order somewhere. << For example, if I want to do a WHERE IN or WHERE NOT IN list comparison having the most likely to match items first in the list speeds up the search and uses less computer resources. >> Two things: In a IN or NOT IN comparison I expect R:Base would, in fact, order the result set using the ORDER BY term. That is because it's possible to do so — the subquery has it's OWN Order By clause that can be used for this purpose. Also, I would be very, very surprised if the order of the result set made any difference in the speed of execution of this kind of query. R:Base is so fast on these queries that it is (almost) certainly building an internal index of the results and using that index rather than a straight list-search to qualify rows. -- Larry
