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? Thanks, Mark _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
