Dag �yvind Liodden > ROWNO doesn't applpy to ORDER-statements, does it? The > following statement > returns the same row whether I specify DESC or ASC: > > select * from trade where symbol = 'OPC' and ROWNO < 2 order > by trade_id > desc > > Is this a bug, or is it supposed to be like this? How can I avoid it?
It was discussed several times: ROWNO is applied and THEN the first rows found are sorted. ROWNO is used to avoid scanning the whole table. If you want to see the highest trade_id you have to scan the whole table (without specifying ROWNO) and then fetch the number of rows (in your case 1) you want. If you have a desc index on trade_id and no condition in your WHERE clause which let the kernel-optimizer decide for another index/key-range because of this condition, then the optimizer will often decide for the index which fits the order by-clause. Then the kernel will not scan the whole table and sort, but fetch the rows according to the index when the application says FETCH (give me the next resultrow). In this case NO ROWNO-specification has to be in the WHERE-clause. Just ask for one row by just one FETCH (or the corresponding call in your client) and you will receive the highest value of trade_id. Elke SAP Labs Berlin _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
