MSSQL: left join on MAX()

Question

Is there a better (more performant) way to join on the MAX(id) (without using a subselect) than my actual way? We are dealing with huge databases and are trying to optimize as much as possible

SELECT e.*, r.report_name FROM report r 
LEFT JOIN engineer e ON e.id = r.id
WHERE e.id = (SELECT MAX(id) FROM engineer WHERE process = r.process)

Thanks in advance!

Answer 1

Maybe this will help you

WHERE e.id = (SELECT top 1 id 
              FROM engineer 
              WHERE process =r.process
              order by id desc)

Answer 2

I think thats better

SELECT TOP 1 e.*, r.report_name FROM report r 
LEFT JOIN engineer e ON e.id = r.id
ORDER BY e.id desc

Answer 3

One other option: pre-aggregate the entire set, then JOIN to it to filter your other tables. This will treat it like a table, rather than executing the same SELECT for every single row that you run into; set-based logic versus row-by-agonizing-row.

SELECT 
    e.*, 
    r.report_name 
FROM 
    report r 
     INNER JOIN -- the WHERE clause effectively makes this an INNER JOIN, so changed for readability
    engineer e ON 
        e.id = r.id
     INNER JOIN 
      (
        SELECT Process, MAX(ID) MaxID
        FROM Engineer 
        GROUP BY Process 
      ) eMax ON 
        e.Process = eMax.Process AND
        e.ID = eMax.MaxID