0

I have following query:

SELECT p.product_name, SUM(o.quantity) no_of_product_sale
  2  FROM Order_Address_Quantity o
  3  JOIN Product p ON p.product_code = o.product_id
  4  GROUP BY p.product_name;

which results in:

PRODUCT_NAME                   NO_OF_PRODUCT_SALE
------------------------------ ------------------
Coffee                                         10
Noodles                                         4
Oil                                             8
Rice                                            5
Dishwasher                                      3
Biscuits                                        2

6 rows selected.

How can I show the product with maximum no_of_product_sale? In other SQLs it can be done by adding LIMIT = 1 clause.

Samit Paudel
  • 181
  • 7
  • 1
    For the record, in case someone stumbles here, from Oracle 12 onwards you can use the SQL:2008 standard `FETCH FIRST N ROWS ONLY` clause https://stackoverflow.com/a/57484427/14955 – Thilo Jan 31 '21 at 11:10

1 Answers1

1

You need to add an ORDER BY clause to sort your entries from highest to lowest and then you can use an outer SELECT to get the first entry out of it:

SELECT *
FROM 
(SELECT p.product_name, SUM(o.quantity) no_of_product_sale
FROM Order_Address_Quantity o
JOIN Product p ON p.product_code = o.product_id
GROUP BY p.product_name
ORDER BY 2 DESC )
WHERE ROWNUM = 1;
Neo
  • 779
  • 5
  • 13