Mark Wong wrote: > On Thu, 2002-02-07 at 22:42, Zabach, Elke wrote: > > Mark Wong wrote: > > > > > I don't think anyone asked this variation (or my eyes are > > > getting tired > > > from searching.) > > > > > > What if we want the result of the first 10 rows to be in a > > > subquery? Is > > > there a way to use fetch to do that? For example (using TOP) > > > I want to > > > see which customers made the most recent 1000 orders: > > > > > > SELECT DISTINCT order_customer_id, customer_name > > > FROM (SELECT TOP 1000 order_id, order_customer_id > > > FROM order_table > > > ORDER BY order_date DESC), customer_table > > > > > > Or am I already in trouble because we can't use an ORDER BY > > > clause in a > > > subquery? I hope I did that example correctly. :-) > > > > 1. Yes, you are in trouble, because ORDER BY will not work > in a subquery. > > 2. All those mails written in this list concerning 'the > first x rows AFTER > > ORDERING' > > are true for your example > > 3. perhaps this is a chance for you: > > > > declare myresult cursor for > > SELECT order_id, order_customer_id > > FROM order_table > > ORDER BY order_date DESC > > > > SELECT DISTINCT order_customer_id, customer_name > > FROM (select * from myresult where rowno <= 1000), customer_table > > > > CLOSE myresult > > handle the real result > > > > Elke > > SAP Labs Berlin > > > > > > That certainly sounds like a possibility. Can I safely assume that > declaring that cursor will still perform a full table scan on my > order_table?
You can be assured that a full table scan is done for order_table. If ALL columns needed for this select would be part of an index (index-columns + primary key columns) it depends on the size of primary table and index if not a scan of the primary table, but of the index was performed. But a FULL scan of one of those tables would be done either. BTW: to assure that myresult can be reused in the next select the syntax-clause FOR REUSE can be appended to the declare cursor given above. Then even if an andex for order_date existed (and myresult would usually not been build physically) it IS build and can be reused then. FOR REUSE is available in SQLMODEs internal and oracle. Elke SAP Labs Berlin _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
