Returning MAX function on query?

Question

I need to find the Stock Item with the greatest profit margin, however am unsure on how to do so as this was calculated within the query and not taken from a table. My script is as follows (Using Oracle 11g), and currently returns several stock items when I need only one. Please help

SELECT (UPPER(Orderline.StockID) || ' ' || INITCAP(StockDesc)) AS "Item",
CategoryDesc AS "Category",
LocationCity AS "Warehouse",
Quantity AS "Number Sold",
TO_CHAR(Quantity * UnitPrice) AS "Total Income",
TO_CHAR(Quantity * UnitCost) AS "Total Cost",
TO_CHAR((Quantity * UnitPrice) - (Quantity * UnitCost)) AS "Profit"
FROM Orderline INNER JOIN 
(Stock INNER JOIN 
("CATEGORY" INNER JOIN Warehouse
ON "CATEGORY".Warehouse = Warehouse.WarehouseID)
ON Stock.ItemCategory = "CATEGORY".CategoryID)
ON Orderline.StockID = Stock.StockNo
ORDER BY ((Quantity * UnitPrice) - (Quantity * UnitCost)) DESC;

Wrap your query in `select * from (... your query here ...) x where rownum = 1` (per Bacon Bit's suggestion) — Andomar, May 28 '15 at 12:34
@JessRap highest profit per.....? item? category? warehouse? — Matt, May 28 '15 at 12:34
@Andomar That does not work without a subquery, inline view, or WITH clause. WHERE is before ORDER BY. — Bacon Bits, May 28 '15 at 12:36
@Andormar I tried this, but it returned the first item listed, not that with the greatest profit ... — JessRap, May 28 '15 at 12:45
@miraclefoxx yes exaclty, highest profit with regards to the item, just unsure on how to do it — JessRap, May 28 '15 at 12:52

score 0 · Accepted Answer · edited May 23 '17 at 11:43

try this...

select * from (SELECT (UPPER(Orderline.StockID) || ' ' || INITCAP(StockDesc)) AS "Item",
        CategoryDesc AS "Category",
        LocationCity AS "Warehouse",
        Quantity AS "Number Sold",
        TO_CHAR(Quantity * UnitPrice) AS "Total Income",
        TO_CHAR(Quantity * UnitCost) AS "Total Cost",
        TO_CHAR((Quantity * UnitPrice) - (Quantity * UnitCost)) AS "Profit"
        FROM Orderline INNER JOIN 
        (Stock INNER JOIN 
        ("CATEGORY" INNER JOIN Warehouse
        ON "CATEGORY".Warehouse = Warehouse.WarehouseID)
        ON Stock.ItemCategory = "CATEGORY".CategoryID)
        ON Orderline.StockID = Stock.StockNo
        ORDER BY Profit DESC)
        where rownum = 1;

I've seen in the comments that you've tried rownum=1 on the inner select... This would select the only Item and sort it with order by. There's not many ways to sort a set of one element. But if you'll have sorted set of all elements, you can just pick the first. That's what happens here.

See more info about this case here.

OH, THIS WORKED! Thank you so much! – JessRap May 28 '15 at 13:10 — JessRap, May 28 '15 at 13:10

Returning MAX function on query?

1 Answers1