SQL - Oracle - how to select securities from table with top change %

Question

I need some help to complete a query in SQL-Oracle. I trying to have it select the row(s) or security/ies with the highest or max gain from price1 to price2. here is what i have already. When I try to use sub queries in the select or where clauses, fails....and also i cannot use queries that are hard-quoted because the data in the table will increase periodically to 1000 securites. Any help is appreciated

My query:

select security, 
       price1,
       price2,
       ((price2 - price1)/price1)*100 as Percentage
  FROM market

My table and data

CREATE TABLE market
(security   VARCHAR2(30) PRIMARY KEY,
PRICE1 NUMBER(6, 2),
PRICE2 NUMBER(6, 2));

INSERT INTO market VALUES('A', 62, 178);
INSERT INTO market VALUES('B', 80, 328);
INSERT INTO market VALUES('C', 72, 736);
INSERT INTO market VALUES('D', 270, 565);
INSERT INTO market VALUES('E', 570, 665);
INSERT INTO market VALUES('F', 400, 1640);
INSERT INTO market VALUES('G', 800, 3280);

COMMIT;

Although your example doesn't show it, I assume that each single security will have multiple rows with different price1/price2 values, is that correct? — Brian DeMilia, Jul 25 '14 at 02:19
Do you want the security with the highest percent change (1 row representing your 'answer', or do you want the highest percent change of each security? — Brian DeMilia, Jul 25 '14 at 02:20
hi brian....for this table there will only be 2 prices. price 1 and price2 — DaviRoli, Jul 25 '14 at 02:27

score 2 · Answer 1 · answered Jul 25 '14 at 02:22

2

Here is another alternative.

select * from
  (select security, price1, price2, ((price2 - price1)/price1)*100 as Percentage
   from market
   order by ((price2 - price1)/price1)*100 desc
  )
where rownum = 1
;

answered Jul 25 '14 at 02:22

codenheim

20,467
1
59
80

This wont handle ties between securities. Since you have other solutions I'll leave it as is, rather than rewrite it, since I'd likely end up emulating that of Gordon's. – codenheim Jul 25 '14 at 03:29

score 1 · Accepted Answer · answered Jul 25 '14 at 02:13

1

There are several ways of doing this. Here is one way:

select m.*
from (select security, price1, price2, ((price2 - price1)/price1)*100 as Percentage,
             dense_rank() over (order by ((price2 - price1)/price1)*100 desc) as ranking
      from market
     ) m
where ranking = 1;

answered Jul 25 '14 at 02:13

Gordon Linoff

1,242,037
58
646
786

thanks Gordon. This one worked as well even after i populated the table with new data. – DaviRoli Jul 25 '14 at 03:05

score 1 · Answer 3 · answered Jul 25 '14 at 02:26

1

 select m.security, x.max_pct_chg
  from market m
  join (select max(((price2 - price1) / price1) * 100) as max_pct_chg
          from market) x
    on ((m.price2 - m.price1) / m.price1) * 100 = x.max_pct_chg

http://sqlfiddle.com/#!4/182cb0/3/0

answered Jul 25 '14 at 02:26

Brian DeMilia

13,103
1
23
33

awesome!!!!.... thank you soo... much B. I didn't think it was possible to do it without the 'where' clause. I did populate the table with new data where I now have five securities with the same top percentage increase and all 5 came up in the results. – DaviRoli Jul 25 '14 at 03:03
@user3650350 no problem, yes, this solution will keep ties – Brian DeMilia Jul 25 '14 at 03:05

SQL - Oracle - how to select securities from table with top change %

3 Answers3