title: T-SQL JOIN to get max value
body: <p>First up can I apologise in advance as I know there are loads of questions and answers about this but I simply cannot get my head around it!</p>
  
  <p>I have a table which contains a customer reference number <code>[MT-CODE]</code>, an invoice reference number <code>[REFERENCE]</code> and a date <code>[POST-DATE]</code> which I can use <code>DATEDIFF</code> on to see if the bill is overdue.  </p>
  
  <p>The columns <code>[MT-CODE]</code> and <code>[REFERENCE]</code> are not unique and so when I want to see if any of the invoices are overdue I can use grouping with the idea that I get one row per <code>[REFERENCE]</code>.</p>
  
  <p>My current query is as follows:</p>
  
  <pre><code>SELECT 
      nt.[MT-CODE], nt.[REFERENCE], 
      DATEDIFF(day, nt.[POST-DATE], GETDATE()) AS LateDays
  FROM
      pub1_nomtrans nt
  WHERE 
      ([UNDONE-FLAG] = 0) 
      AND ([POST-TYPE] = N'BC') 
      AND ([CONTRA-CODE] = N'X20') 
      AND (ISNULL([BILLED-PAID-FLAG], N'') &lt;&gt; 'C') 
      AND [Reference] = '1800509'
  GROUP BY 
      nt.[MT-CODE], nt.[REFERENCE], nt.[POST-DATE]
  </code></pre>
  
  <p>This currently returns 2 rows:</p>
  
  <pre><code>MT-CODE REFERENCE   LateDays
  ----------------------------
  012790/0033 1800509    93
  012790/0033 1800509    89
  </code></pre>
  
  <p>What I would like to do is do a join on itself so that I get the maximum late days figure for each row - i.e. the output for this query should be:</p>
  
  <pre><code>MT-CODE REFERENCE   LateDays
  ----------------------------
  012790/0033 1800509    93
  </code></pre>
  
  <p>Any pointers gratefully received.</p>
  
  <p>Thank you</p>