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. :-)
I'm up for any other suggestions too!
Thanks,
Mark
On Fri, 2002-01-18 at 01:30, Zabach, Elke wrote:
> Pedro B. Gomes Costa wrote
>
> I recently discovered (Konstantin V. Oussanov helped me) that we can use
> "WHERE ROWNO < n" to TRY to acomplish what "TOP n" or SET ROWCOUNT do on SQL
> Server.
>
> So i try to make the same as
> SELECT TOP 10 * FROM TABLE ORDER BY TABLE_COLUMN
> with
> SELECT * FROM TABLE WHERE ROWNO < 10 ORDER BY TABLE_COLUMN
>
> The problem, as stated on SAPDB documentation is that this two sql sentences
> produce very diferent results!!!!
> SAPDB first take the 10 rows and then order them, and i want to order the
> table then take the 10 first rows as SQL server (and every other DBMS's i've
> used so far) do.
>
> First i thought about doing this:
>
> SELECT * FROM (SELECT * FROM TABLE ORDER BY TABLE_COLUMN) WHERE ROWNO < 10
>
> But its seems sapDB cant take an "order by" clause on a subquery.....
> My problem is that i simply cant understand well the documentation... I
> think that its very pretty but its strange and difficult to read and find
> what we want (to many links and definitions).
>
> Can someone help me with this ?. Its really important to me to be able to do
> this on a SQL statement (i have some clues on how to do itusing stored
> procedures, but i cant). I was finding SAPDB very nice, but without this
> feature i will have to drop it.
>
>
> If you want to receive the first 10 rows AFTER ordering ALL rows:
> SELECT * FROM table order by table_column
> fetch (10 times)
>
> Do NOT rely on any strategy and therefore implicit ordering because of
> index/key-columns
> used for ordering. They may change.
>
> Elke
> SAP Labs Berlin
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
