• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4837
  • Last Modified:

Oracle Loop Query

Hello, I have a fairly simple query im trying to do, but im getting the syntax wrong somewhere. Basically I have this query which would yield the following.:
select distinct sector from sym.symbol order by 1
Energy
Materials
Technology etc....
now what I want to do is get the top 5 symbols from my database for each of these topics and my first attempt was to do this but it is not working, and Im guessing I need a FOR or WHILE loop...

Here is the query that will get what I want if I manually type in each sector.

  select t.symbol, sum(vol ) from xxxx.trade t, sym.symbol s where trunc(trade_time) > trunc(sysdate)
    and t.symbol=s.symbol and nvl(s.test_symbol,0)<>1
    and s.sector =  'Materials'
    group by t.symbol order by 2 DESC



0
Extreme66
Asked:
Extreme66
  • 3
1 Solution
 
Jinesh KamdarCommented:
select * from
(select t.symbol, sum(vol) from xxxx.trade t, sym.symbol s where trunc(trade_time) > trunc(sysdate)
    and t.symbol=s.symbol and nvl(s.test_symbol,0)<>1
    and s.sector =  'Materials'
    group by t.symbol order by 2 DESC)
where rownum <= 5;
0
 
Jinesh KamdarCommented:
sorry, forgot to comment the filter on sector.

select * from
(select t.symbol, sum(vol)
  from xxxx.trade t, sym.symbol s
  where trunc(trade_time) > trunc(sysdate)
   and t.symbol=s.symbol and nvl(s.test_symbol,0)<>1
--  and s.sector =  'Materials'
    group by t.symbol order by 2 DESC)
where rownum <= 5;
0
 
Extreme66Author Commented:
Hi, I forgot to add the rownum part, this doesyield the top 5 for the sector, but I am looking to have this query run for each of the sectors in a loop.
to get the following ...

Energy
aaa
bbb
ccc
ddd
eee
Materials
aaa
bbb
ccc
ddd
eee
Technology
aaa
bbb
ccc
ddd
eee
etc...
0
 
Jinesh KamdarCommented:
Could you post ur exact reqd. result-set? I dont the see the sum(vol) column in ur result-set.
Also a few sample records from the tables would help a lot.
0
 
NicksonKohCommented:
Hi Extreme66,

Here's a solution using analytics

SELECT *
  FROM (SELECT s.sector, t.symbol, totvol,
               DENSE_RANK () OVER (PARTITION BY s.sector ORDER BY totvol DESC)
                                                                        drank
          FROM (SELECT   s.sector, t.symbol, SUM (vol) totvol
                    FROM xxxx.trade t, sym.symbol s
                   WHERE TRUNC (trade_time) > TRUNC (SYSDATE)
                     AND t.symbol = s.symbol
                     AND NVL (s.test_symbol, 0) <> 1
                GROUP BY s.sector, t.symbol))
 WHERE drank <= 5

Cheers,
NicksonKoh
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now