Create Top-N Query?

[Pozzi]

Hi,

Below is a query that works just fine: -

SELECT gr_code, ar_code, ar_description, sum_value, sum_qty,
sum_margin / sum_qty, gr_description
FROM
(SELECT GR.CODE AS gr_code, AR.CODE AS ar_code, AR.DESCRIPTION AS ar_description,
SUM(TA.PRICE + NVL(TA.DISCOUNT,0) + NVL(TA.SERVICE,0)) AS SUM_VALUE,
SUM(DECODE(AR.MEAS_UNIT,1,TA.QTY_WEIGHT,1)) AS SUM_QTY,
NVL(SUM(((TA.PRICE + NVL(TA.DISCOUNT,0) + NVL(TA.SERVICE,0)) - AR.REFERENCE_PRICE) /
(TA.PRICE + NVL(TA.DISCOUNT,0) + NVL(TA.SERVICE,0))) * 100,0) AS SUM_MARGIN,
GR.DESCRIPTION AS gr_description
FROM
TB_TRANSACTIONS TR, TB_TRANS_ARTICLES TA, TB_ARTICLES AR, TB_GROUPS GR,
TB_CASHREGS CA, TB_SHOPS SH, TB_PRICELEVELS PL
WHERE TR.BOOKKEEPING_DATE
BETWEEN TO_DATE('13.12.2002', 'dd.mm.yyyy')
AND TO_DATE('13.12.2002', 'dd.mm.yyyy')
AND TA.TRANSACTION_ID = TR.ID
AND AR.ID = TA.ARTICLE_ID
AND AR.TYPE IN (0)
AND TR.DELETE_OPERATOR_ID IS NULL
AND CA.ID = TR.CASHREG_ID
AND SH.ID = CA.SHOP_ID
AND TR.PRICELEVEL_ID = PL.ID
AND GR.ID (+) = AR.GROUP_A_ID
GROUP BY GR.CODE, AR.CODE, AR.DESCRIPTION, GR.DESCRIPTION)
ORDER BY gr_code, sum_margin / sum_qty DESC

I want to use the same query but only return X rows, by using an additional WHERE clause i.e. ROWNUM = X

The problem is I have no idea how to achieve it.

Any help or advice would be greatly appreciated.

Regards

 

[blom0344]

Hello Pozzi,

Did you consider working in a rank() object to get a real sequential field where to put the condition on?
If you do an order by gr_code and sum_margin / sum_qty you will have to repeat it in the rank expression:

rank() over(gr_code,sum_margin / sum_qty) as ranking

I do not know from which version on in ORACLE you can use this OLAP function (I think from 8i onwards, but possibly only in Enterprise edition) T. Blom
Information analyst
tbl@shimano-eu.com

 

[did02]

Hi,

If I well understood, just try to add this clause :
where rownum <= X before the order by.

Or maybe you can insert this clause in the sub-query.

Hope it helps u.
Rgds,
Did02

 

[srishan]

1. I took out your inner query as it is too long to type, please replace ... notation with your actual query.
2. Added a column alias to sum_margin/sum_qty as
ratio_margin_qty.
3. Added rownum <= n condition to the outmost query.

P.S You can not add rownum cluase anywhere inside to get the correct results because you want the resultset to me limited AFTER the ORDER by is DONE.


SELECT * FROM
(

SELECT gr_code, ar_code, ar_description, sum_value, sum_qty,
sum_margin/sum_qty ratio_margin_qty, gr_description
FROM
(SELECT ....
....
)
ORDER BY gr_code, sum_margin / sum_qty DESC

)
WHERE ROWNUM <= n;


HTH,
SriDHAR

 

[did02]

I disagree with you : you can keep the rownum clause inside.

SELECT field1, field2, ...
FROM table1
where rownum <5
order by field1;

Oracle retrieves all rows, then sort them and finaly display the N first rows.

Regards,
Did02

 

[carp]

Nope. While Oracle retrieves the rows, sorts them, and displays them (as you say), it only retrieves the rows as specified by your WHERE clause! If you say &quot;WHERE rownum < 3&quot;, then Oracle will retrieve the first two rows it finds, sort them, and display them.

SQL> select * from test;
X
-----
6
56
46
36
3
2
1

SQL> select * from test
2 where rownum < 3
3 order by 1;
X
-----
6
56

SQL> select *
2 from (select *
3 from test
4 order by 1)
5 where rownum < 3;
X
-----
1
2

&quot;An ounce of experience is worth a pound of expert opinion.&quot;

 

[carp]

The good news is that Oracle is now smart enough not to sort the entire table! If it sees that you only want the top n values, it stops sorting when it has found the top n values:

1 select *
2 from (select *
3 from test
4 order by 1)
5* where rownum < 3;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 COUNT (STOPKEY)
2 1 VIEW
3 2 SORT (ORDER BY STOPKEY)
4 3 TABLE ACCESS (FULL) OF 'TEST'