SQL - Select Fields of the Max ID

Question

The last InvoiceID, and corresponding fields needs to be selected. The entire sql contains several inner joins.

SELECT max(InvoiceID), 
       InvoiceEndDate 
  FROM Invoices 
 WHERE TransactionOrderItemID = '000831'

Right now, I am getting the InvoiceID, and have to fetch the InvoiceEndDate again.

Is there an efficient way of doing this?

Answer 1

SELECT InvoiceID, InvoiceEndDate 
FROM Invoices 
WHERE TransactionOrderItemID='000831'
ORDER BY InvoiceID DESC
LIMIT 1

Answer 2

SELECT InvoiceID, InvoiceEndDate 
FROM Invoices INV
WHERE TransactionOrderItemID='000831'
  AND INV.InvoiceID = (SELECT MAX(SUB.InvoiceID)
    FROM Invoices SUB WHERE SUB.TransactionOrderItemID='000831');

Answer 3

Take a look at Including an Aggregated Column's Related Values which has several ways to accomplish this