title: Easiest way to limit JOIN to only select TOP 1
body: <p>I am using the following query for the main level of a procedure with two nested queries. </p>
  
  <p>The query works and selects the correct data, my only issue is that I would only need the <code>TOP 1</code> record from the <code>JOIN</code> based on an <code>ORDER BY modTime desc</code>. </p>
  
  <p>I found a few posts on this on Google with very different approaches and was not sure which one is the right one here. Example source: <a href="https://stackoverflow.com/questions/3375436/how-do-i-limit-a-left-join-to-the-1st-result-in-sql-server">How do I limit a LEFT JOIN to the 1st result in SQL Server?</a></p>
  
  <p>I like the way of wrapping an Inner Join in the Left Join but wasn't sure if that makes sense and how to apply it here. What would be the easiest approach and how to apply this here?</p>
  
  <p>My SQL:</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[MOC_FetchMenu]
  AS
  BEGIN
      SET NOCOUNT ON;
      SELECT      A.itemID,
                  COALESCE(A.parentID, '999999'),
                  A.itemName,
                  A.linkRef,
                  A.sortID,
                  COUNT(*) OVER() AS volume,
                  D.lastUpdate,
                  CONVERT(VARCHAR(11), D.modTime, 106) AS modTime,
                  D.modBy,
                  '---' AS levelSub,
                  (
                    -- begin of my nested queries
                  )
      FROM        MOC_Links A
      LEFT JOIN   MOC_Log D
      ON          D.itemID = A.itemID
      WHERE       A.parentID = '1'
      ORDER BY    A.itemName
      FOR XML PATH('level1'), ELEMENTS, TYPE, ROOT('ranks')
  
  END
  </code></pre>