-1

My question asks me to "Display the trade_id, the stock_id, and the total price for the trade with the highest price total.

There is only 1 table I need to work with and that's the trade table.

Currently I have:

select trade_id,stock_id,max(price_total)
from trade
group by trade_id, stock_id;

I need to fetch the row highest trade (which I know the trade_id, and stock_id and price total for), but my Query is displaying a wide range of rows, I only need 1 row, which is the row with the highest price_total and it's corresponding stock_id and trade_id. Does anyone know how to fetch only the 1 row asked.

P.S: I know which row it is, I just can't seem to return that row only in my query result. Thank you!

Peter Kissoon
  • 1
  • 1
  • 1
  • Try using `HAVING` clause. – PM 77-1 Feb 11 '17 at 02:07
  • What does the requirement say for the special case when two or more trades are *tied* for highest price total? (By the way, if the requirement is silent about this, then it's a bad requirement.) –  Feb 11 '17 at 02:14
  • Try **stackoverflow oracle select row with max value** in Google, you will find all the possible solutions (and many impossible ones). Learn to do some research for yourself. –  Feb 11 '17 at 02:17
  • I tried google and stack verflow but my search returned with hardcoding and questions that were irrelevant to what I searched for. – Peter Kissoon Feb 13 '17 at 00:58

1 Answers1

0

Below query will give you desired result...check


    select * from 
        (select trade_id,stock_id,max(price_total) as max_price_total
        from trade
        group by trade_id, stock_id
        order by 3 desc)
    where rownum = 1;
Rakesh
  • 84
  • 7