I have two tables (parent-child model): Loans & Transactions.
Each Loan row has multiple Transaction rows.
I want to select all rows from Loans together with most recent row from Transactions.
Here is my select to achieve this.
SELECT L.*, T.* FROM Loans L CROSS APPLY ( SELECT TOP 1 * FROM Transactions AS T WHERE L.LoanID = T.LoanID ORDER BY CRTime DESC ) T
SELECT L.*, T.* FROM Loans L INNER JOIN ( SELECT MAX(TransactionID) AS TransactionID, LoanID FROM Transactions AS T GROUP BY LoanID ) T ON T.LoanID = L.LoanID INNER JOIN Transactions TS ON TS.TransactionID = T.TransactionID
Any ideas on improving it?
Side question: Can you suggest best practices for comparing two queries running times?