----- Original Message -----
Sent: Friday, January 18, 2002 9:30
AM
Subject: RE: WHERE ROWNO <= n
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
