Home » SQL & PL/SQL » SQL & PL/SQL » Urgent: Finding the top 10 NET- SALES- FIGURES
Urgent: Finding the top 10 NET- SALES- FIGURES [message #20927] Tue, 02 July 2002 03:34 Go to next message
Deborrah
Messages: 8
Registered: July 2002
Junior Member
Hi,
I am having query on the Sales table, where
I have to fetch the top 10 sales figures
group by PRODUCT_CODE.

I have the sales figures = AMT1 + AMT2 + AMT3
each of which exist in the same table. I just
sum them up group by PRODUCT_CODE.

How can I get only the top 10 sales figures?

TIA,

Debby
Re: Urgent: Finding the top 10 NET- SALES- FIGURES [message #20929 is a reply to message #20927] Tue, 02 July 2002 04:56 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
you can use something like this.
this query give top 4 group of salaries based on job
SQL> ed
Wrote file afiedt.buf

  1  select * from (
  2   select job,sum(sal) s from emp group by job) a
  3  where rownum <= 4
  4* order by s
SQL> /

JOB                S
--------- ----------
CLERK        3462.23
PRESIDENT       5000
ANALYST         6000
MANAGER         8275

SQL> 
Re: Urgent: Finding the top 10 NET- SALES- FIGURES [message #20971 is a reply to message #20929] Wed, 03 July 2002 20:35 Go to previous messageGo to next message
Deborah
Messages: 18
Registered: June 2002
Junior Member
Hi Mahesh,
Thanks for the reply. It works fine for Ora 8.0.5
onwards.

But I have Oracle 7.3.4. It does not allow an order
by clause in teh subquery. Please advise an alternate
solution. Because the server where I will install my
program has Oracle 7.3

TIA,
Debby
Re: Urgent: Finding the top 10 NET- SALES- FIGURES [message #20976 is a reply to message #20929] Thu, 04 July 2002 01:08 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
this is old sql
I am sorry , i gave you a wrong information.
Actually, this is not a correct method (Eventhough
it MAY give the expected result MOSTly)to derive 
the TOP N rows.
Becuse we are ordering the rows, out of subquery.
so this should work fine in 7.x also. 
( must be > 7.1.6)
I have no instance with 7.x. So i didnt test it.
SQL> ed
Wrote file afiedt.buf

  1  select job,s from
  2   (select job,sum(sal) s from emp group by job)
  3   where rownum <= 4
  4*  order by s
SQL> /

JOB                S
--------- ----------
CLERK           4150
PRESIDENT       5000
ANALYST         6000
MANAGER         8275

this is the new query
with 8.1.5.x and greater you can restructure the query as follows.
The nested query first selects ALL rows from the table and sorts
them in ascending order.
SQL> ed
Wrote file afiedt.buf

  1  select * from
  2  (
  3  select job,sum(sal) s from emp group by job order by s) a
  4*   where rownum <= 4
SQL> /

JOB                S
--------- ----------
CLERK           4150
PRESIDENT       5000
SALESMAN        5600
ANALYST         6000

take a note on differences in the output for the above queries. The second one is more accurate.
I am still working on you requirement with 7.3x
Re: Urgent: Finding the top 10 NET- SALES- FIGURES [message #20980 is a reply to message #20929] Thu, 04 July 2002 03:35 Go to previous messageGo to next message
M.P.KIRAN KUMAR
Messages: 9
Registered: May 2002
Junior Member
SELECT *
FROM my_table a
WHERE 10 >= (SELECT COUNT(DISTINCT maxcol)
FROM my_table b
WHERE b.maxcol >= a.maxcol)
ORDER BY maxcol DESC;
Re: Urgent: Finding the top 10 NET- SALES- FIGURES [message #20982 is a reply to message #20927] Thu, 04 July 2002 04:56 Go to previous message
Rohit Mehrotra
Messages: 9
Registered: July 2002
Junior Member
hi ,
try this one...

select id,tot from
(select id,amt1+amt2+amt3 tot,count(*)
from x group by id,amt1+amt2+amt3
order by 2 desc ) where rownum <= 10

Regards
Rohit
Previous Topic: converting comma seperated CHAR to Number
Next Topic: Re: How can I order by the PL/SQL table?
Goto Forum:
  


Current Time: Wed May 08 22:39:00 CDT 2024