Get just the first row for each company

Question

I have this

Select DISTINCT  p.productname, c.companyname,od.productID,
(SELECT
    sum( quantity )
FROM orderdetails where productID=p.ProductID) as total
from 
customers c inner join orders o on
c.customerid=o.customerid inner join orderdetails od on
o.orderid=od.orderid inner join products p on
od.productid=p.productid

order by c.companyname, total DESC

and that returns me return I need it to only return the row of the highest total for each companyname I WANT JUST THE MARKED ONES

You have mistankenly tagged two different DBMS, MySQL and Oracle. Which are you using? — Thorsten Kettner, Oct 01 '21 at 06:37
Does this answer your question? [Get top n records for each group of grouped results](https://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results) — astentx, Oct 01 '21 at 08:32
Please note that many sites block links, and many participants here refuse to open links for the same security reason others block them. Nothing prevents you from pasting that content directly into your question. Doing so is much appreciated by all, and will generally be more conducive to receiving a positive response. — EdStevens, Oct 01 '21 at 13:43

score 0 · Answer 1 · answered Oct 01 '21 at 05:26

0

you can use Max property to get the maximum total.

MAX(aggregate_expression)
FROM tables

answered Oct 01 '21 at 05:26

H.M.Mubashir

170
1
14

score 0 · Answer 2 · answered Oct 01 '21 at 06:34

One option is to use your current query (if you're satisfied with it) as a "source" (either a CTE - as in my example, or a subquery) for ranking rows per TOTAL column value for each COMPANYNAME, and then return the highest ranked rows.

WITH
   your_query
   AS
      (SELECT DISTINCT p.productname,
                       c.companyname,
                       od.productID,
                       (SELECT SUM (quantity)
                          FROM orderdetails
                         WHERE productID = p.ProductID) AS total
         FROM customers c
              INNER JOIN orders o ON c.customerid = o.customerid
              INNER JOIN orderdetails od ON o.orderid = od.orderid
              INNER JOIN products p ON od.productid = p.productid),
   temp
   AS
      -- rank TOTAL values per each COMPANYNAME
      (SELECT productname,
              companyname,
              productid,
              total,
              RANK () OVER (PARTITION BY companyname ORDER BY total DESC) rnk
         FROM your_query)
  -- finally, return rows whose TOTAL ranks as 1st (in descending order)         
  SELECT productname,
         companyname,
         productid,
         total
    FROM temp
   WHERE rnk = 1
ORDER BY companyname

Get just the first row for each company

2 Answers2