How to efficiently select the rows corresponding with the highest value for a specific column?

Question

I have a query:

SELECT * FROM `InvoiceLines`
INNER JOIN Invoices ON Invoices.`invoiceID` = InvoiceLines.`invoiceID`
INNER JOIN `DistributorOrdersTbl` ON DistributorOrdersTbl.`distOrderRecID` = Invoices.`orderID`
WHERE DistributorOrdersTbl.`distOrderRecID` = 3829
AND InvoiceLines.`qty` > 0

Which returns a number of Invoices:

I wanted to select only the invoices with the newest ID 2800. The way I ended up doing it was a bit of copy pasting:

SELECT * FROM `InvoiceLines`
INNER JOIN Invoices ON Invoices.`invoiceID` = InvoiceLines.`invoiceID`
INNER JOIN `DistributorOrdersTbl` ON DistributorOrdersTbl.`distOrderRecID` = Invoices.`orderID`
WHERE DistributorOrdersTbl.`distOrderRecID` = 3829
AND InvoiceLines.`qty` > 0
AND Invoices.invoiceID = (
        SELECT MAX(Invoices.invoiceID) FROM `InvoiceLines`
        INNER JOIN Invoices ON Invoices.`invoiceID` = InvoiceLines.`invoiceID`
        INNER JOIN `DistributorOrdersTbl` ON DistributorOrdersTbl.`distOrderRecID` = Invoices.`orderID`
        WHERE DistributorOrdersTbl.`distOrderRecID` = 3829
        AND InvoiceLines.`qty` > 0
    )

And sure enough, the correct invoices are returned. However it feels as though this is inefficient as I am essentially performing the query twice. What would be the best way of doing this? I tried the following but it seems this is incorrect SQL:

SELECT * FROM `InvoiceLines`
INNER JOIN Invoices ON Invoices.`invoiceID` = InvoiceLines.`invoiceID`
INNER JOIN `DistributorOrdersTbl` ON DistributorOrdersTbl.`distOrderRecID` = Invoices.`orderID`
WHERE DistributorOrdersTbl.`distOrderRecID` = 3829
AND InvoiceLines.`qty` > 0
AND Invoices.invoiceID = MAX(Invoices.invoiceID)

Which returns "Invalid use of group function".

Thanks in advance!

Possible duplicate of [Get records with max value for each group of grouped SQL results](https://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results) .. i would stay very away from the accepted answer because it's a "hack" and misusing MySQL's `GROUP BY` feature. — Raymond Nijland, Jul 17 '18 at 11:34
`MAX` can only be used in `SELECT` statement, but you can use sub-query: `AND Invoices.invoiceID = (SELECT MAX(Invoices.invoiceID) FROM WHERE InvoiceLines WHERE DistributorOrdersTbl.distOrderRecID = 3829 AND InvoiceLines.qty > 0)`. Check out if this is enough — Ricardo Pontual, Jul 17 '18 at 11:37
"MAX can **only** be used in SELECT statement" @RicardoPontual not completely true.. MAX can also be used directly in a `HAVING` clause https://www.db-fiddle.com/f/wTQ1PdwoACSH9b87PjeQPq/0 — Raymond Nijland, Jul 17 '18 at 11:44
@RaymondNijland you're right, I forgot about `having` clause, thank you — Ricardo Pontual, Jul 17 '18 at 11:55
There's a bit more code in the above than is strictly necessary, but essentially, this is how I'd do it. For further help, see: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Jul 17 '18 at 12:15

score 1 · Answer 1 · answered Jul 17 '18 at 11:36

Assuming all invoices have invoice lines, you can use a subquery just on Invoices:

SELECT *
FROM (SELECT i.*
      FROM Invoices i
      WHERE i.orderID = 3829
      ORDER BY i.invoiceID DESC
      LIMIT 1
     ) i JOIN
     InvoiceLines il
     ON i.invoiceID = il.invoiceID JOIN
     DistributorOrdersTbl d
     ON d.distOrderRecID = i.orderID
WHERE il.qty > 0;

How to efficiently select the rows corresponding with the highest value for a specific column?

1 Answers1