OraFAQ Forum: SQL & PL/SQL » Top-N Query Using ROWNUM

Home » SQL & PL/SQL » SQL & PL/SQL » Top-N Query Using ROWNUM

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Top-N Query Using ROWNUM [message #4597]

Mon, 30 December 2002 06:57

Andrew
Messages: 144
Registered: March 1999

Senior Member

I wonder if anyone could advise me on where I'm going wrong with the following query I'm trying to put together?

Below is my current query: -

SELECT G.CODE,A.CODE, A.DESCRIPTION,
SUM(D.PRICE+nvl(d.service,0)) SUM_PRICE
FROM TB_TRANSACTIONS T, TB_TRANS_ARTICLES D, TB_ARTICLES A, TB_GROUPS G,
TB_CASHREGS C, TB_SHOPS M, TB_PRICELEVELS PL
WHERE T.BOOKKEEPING_DATE between to_date('13122002','ddmmyyyy')
and to_date('17122002','ddmmyyyy')
AND TO_CHAR(T.TRANS_DATE,'hh24:mi:ss') between '00:00:00'
AND '23:59:59' AND D.TRANSACTION_ID = T.ID AND A.ID = D.ARTICLE_ID
AND A.TYPE IN (0) AND T.DELETE_OPERATOR_ID IS NULL AND C.ID = T.CASHREG_ID
AND M.ID = C.SHOP_ID AND T.PRICELEVEL_ID = PL.ID AND G.ID (+)= A.GROUP_A_ID
GROUP BY G.CODE, A.CODE, A.DESCRIPTION
ORDER BY G.CODE, SUM_PRICE DESC

This works fine.

The next query also works fine, this time with out any ORDER on the Group Code: -

SELECT G.CODE,A.CODE, A.DESCRIPTION,
SUM(D.PRICE+nvl(d.service,0)) SUM_PRICE
FROM TB_TRANSACTIONS T, TB_TRANS_ARTICLES D, TB_ARTICLES A, TB_GROUPS G,
TB_CASHREGS C, TB_SHOPS M, TB_PRICELEVELS PL
WHERE T.BOOKKEEPING_DATE between to_date('13122002','ddmmyyyy')
and to_date('17122002','ddmmyyyy')
AND TO_CHAR(T.TRANS_DATE,'hh24:mi:ss') between '00:00:00'
AND '23:59:59' AND D.TRANSACTION_ID = T.ID AND A.ID = D.ARTICLE_ID
AND A.TYPE IN (0) AND T.DELETE_OPERATOR_ID IS NULL AND C.ID = T.CASHREG_ID
AND M.ID = C.SHOP_ID AND T.PRICELEVEL_ID = PL.ID AND G.ID (+)= A.GROUP_A_ID
GROUP BY G.CODE, A.CODE, A.DESCRIPTION
ORDER BY SUM_PRICE DESC

What I'd like to do is using the second query limit the number of records shown, so I only get the Top (n) for example the Top 15.

I've looked at the Oracle documentation and have found details regarding ROWNUM and using it to create a 'top-N query'. The example in the documentation is: -

SELECT * FROM
(SELECT empno FROM emp ORDER BY empno)
WHERE ROWNUM < 11:

This returns the 10 smallest employee numbers.

My question is using the above how do I combine it with my query?

I've tried a number of ways but so far haven't been able to get it right :-(

I'd be most gratefull for any assistance you could give.

Many thanks for taking the time to read this & regards,

Andrew

Report message to a moderator

Re: Top-N Query Using ROWNUM [message #4602 is a reply to message #4597]

Mon, 30 December 2002 09:06

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

INSTEAD OF GIVING a
select * from an inline view
u can also give selectively

SQL> ed
Wrote file afiedt.buf

  1  SELECT DEPTNO,SUM(SAL) A
  2  FROM EMP
  3  GROUP BY DEPTNO
  4* order by a desc
SQL> /

    DEPTNO          A
---------- ----------
        20      10875
        30       9400
        10       8750

SQL> ed
Wrote file afiedt.buf

  1  select deptno, s
  2  from
  3  (
  4  SELECT DEPTNO,SUM(SAL) s
  5  FROM EMP
  6  GROUP BY DEPTNO
  7  order by s desc
  8  )
  9* where rownum <2
SQL> /

    DEPTNO          S
---------- ----------
        20      10875

----------------------------------------------------------------------

so, based on that, you try something like~

select a,b,c,sum_price
from
(
SELECT 	G.CODE a, A.CODE b, A.DESCRIPTION c,
SUM(D.PRICE+nvl(d.service,0)) SUM_PRICE
FROM TB_TRANSACTIONS T, TB_TRANS_ARTICLES D, TB_ARTICLES A, TB_GROUPS G,
TB_CASHREGS C, TB_SHOPS M, TB_PRICELEVELS PL
WHERE T.BOOKKEEPING_DATE between to_date('13122002','ddmmyyyy')
and to_date('17122002','ddmmyyyy')
AND TO_CHAR(T.TRANS_DATE,'hh24:mi:ss') between '00:00:00'
AND '23:59:59' AND D.TRANSACTION_ID = T.ID AND A.ID = D.ARTICLE_ID
AND A.TYPE IN (0) AND T.DELETE_OPERATOR_ID IS NULL AND C.ID = T.CASHREG_ID
AND M.ID = C.SHOP_ID AND T.PRICELEVEL_ID = PL.ID AND G.ID (+)= A.GROUP_A_ID
GROUP BY G.CODE, A.CODE, A.DESCRIPTION
ORDER BY G.CODE, SUM_PRICE DESC
)
where rownum < 15

Report message to a moderator

Re: Top-N Query Using ROWNUM [message #4605 is a reply to message #4597]

Mon, 30 December 2002 11:03

Robert Kuhlmann
Messages: 35
Registered: March 2002

Member

Try this
select CODE_g , CODE_a , desc_a, SUM_PRICE
from (SELECT G.CODE code_g,A.CODE code_a, A.DESCRIPTION desc_a, SUM(D.PRICE+nvl(d.service,0)) SUM_PRICE
FROM TB_TRANSACTIONS T, TB_TRANS_ARTICLES D, TB_ARTICLES A, TB_GROUPS G,
TB_CASHREGS C, TB_SHOPS M, TB_PRICELEVELS PL
WHERE T.BOOKKEEPING_DATE between to_date('13122002','ddmmyyyy')
and to_date('17122002','ddmmyyyy')
AND TO_CHAR(T.TRANS_DATE,'hh24:mi:ss') between '00:00:00'
AND '23:59:59' AND D.TRANSACTION_ID = T.ID AND A.ID = D.ARTICLE_ID
AND A.TYPE IN (0) AND T.DELETE_OPERATOR_ID IS NULL AND C.ID = T.CASHREG_ID
AND M.ID = C.SHOP_ID AND T.PRICELEVEL_ID = PL.ID AND G.ID (+)= A.GROUP_A_ID
GROUP BY G.CODE, A.CODE, A.DESCRIPTION
ORDER BY SUM_PRICE DESC)
where rownum < 16;

Report message to a moderator

Previous Topic:	How to insert a long Raw
Next Topic:	Redefining Results of Table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jun 10 14:38:50 CDT 2024