CODEWITHSUNDEEP
mysqlsql
k4kuz0
k4kuz0

Reputation: 1045

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

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:

Result of query

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!

Upvotes: 1

Views: 55

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

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;

Upvotes: 1

Related Questions