Get just the first row for each company-CodePudding

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

CodePudding user response：

you can use Max property to get the maximum total.

MAX(aggregate_expression)
FROM tables

CodePudding user response：

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