On Wed, 30 Jan 2002, Zabach, Elke wrote: > 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:
Sorry about that! :) > 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. OK, that will work but it "feels" wrong. :) Let's say I wanted to use this query in a select * from table where column in "the_query". Returning the whole set would not produce the correct answer. Other RDMBSes have methods like FIRST x, TOP x and LIMIT x that work after the ORDER-clause has been applied. Do you have plans for this in SapDB as well? BTW: The archives tell me that multiple result row dbprocs are in the workings. Do you have any tentative dates for the next release and what it will contain? We have been looking into several DBMSs since our current platform based on Firebird started chokeing under heavy load (JDBC/Interclient + heavy load = Trouble) and we need to make a concrete decision on what to choose soon. DB2, SQL Server 2000 and SapDB are our current options, and although it would be wonderful to use SapDB it seems as it has a few shortcomings, such as the multiple result row dbprocs mentioned and the ROWNO issue). Of course, it might just be that I need some time adapting the new DBMS enviroment and that things just have to be done in a different, but just as good, way. :) Are there any in-depth reviews of SapDB compared to other systems on the web? Anyways, thanks for the great SapDB initiative! Dag _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
