Skip to Content
0
Former Member
Oct 18, 2005 at 06:58 AM

select first rows with rowno

484 Views

Problem: I search for 20 companies with highest turnover in a table. Its a dynamic SQL (created at runtime), I can not use a hard coded fetch of 20 rows.

(Example is a simple table with companyID, turnover and a key)

I read in documentation, that first rowno is applied, then the order (to be fast and not access the whole big table). Ok.

Oracle goes same way, this is the work around:

SELECT * FROM (SELECT t1.Firmid_i AS t1_Firmid_i, SUM( t1.Amount_dc ) AS t1_Amount_dc FROM DEMO.turnover t1 GROUP BY t1.Firmid_i, t1.Productid_i ORDER BY 2 desc ) WHERE rownum <= 20

I try this on a MaxDb :

SELECT * FROM (SELECT t1.Firmid_i, MAX( t1.Amount_dc ) FROM DEMO.turnover t1 GROUP BY t1.Firmid_i ORDER BY 2 asc) WHERE rowno <= 20

-> an error occur (General error;-5016 POS(103) Missing delimiter: ), the order in the inner sql is not allowed

I can change the statement:

SELECT * FROM (SELECT t1.Firmid_i, MAX( t1.Amount_dc ) FROM DEMO.turnover t1 GROUP BY t1.Firmid_i) WHERE rowno <= 20 ORDER BY 2 asc

Now I got 20 row (20 rows sorted), but not the results I need (sort is used to late).

Does anyone knows a workaroud?

TIA

Christian