title: How to Join to first row
body: <p>I'll use a concrete, but hypothetical, example.</p>
  
  <p>Each <strong>Order</strong> normally has only one <strong>line item</strong>:</p>
  
  <p><strong>Orders:</strong></p>
  
  <pre class="lang-none prettyprint-override"><code>OrderGUID   OrderNumber
  =========   ============
  {FFB2...}   STL-7442-1      
  {3EC6...}   MPT-9931-8A
  </code></pre>
  
  <p><strong>LineItems:</strong></p>
  
  <pre class="lang-none prettyprint-override"><code>LineItemGUID   Order ID Quantity   Description
  ============   ======== ========   =================================
  {098FBE3...}   1        7          prefabulated amulite
  {1609B09...}   2        32         spurving bearing
  </code></pre>
  
  <p>But occasionally there will be an order with two line items:</p>
  
  <pre class="lang-none prettyprint-override"><code>LineItemID   Order ID    Quantity   Description
  ==========   ========    ========   =================================
  {A58A1...}   6,784,329   5          pentametric fan
  {0E9BC...}   6,784,329   5          differential girdlespring 
  </code></pre>
  
  <p>Normally when showing the orders to the user:</p>
  
  
  
  <pre><code>SELECT Orders.OrderNumber, LineItems.Quantity, LineItems.Description
  FROM Orders
      INNER JOIN LineItems 
      ON Orders.OrderID = LineItems.OrderID
  </code></pre>
  
  <p>I want to show the single item on the order. But with this occasional order containing two (or more) items, the orders would <em>appear</em> be <strong>duplicated</strong>:</p>
  
  <pre class="lang-none prettyprint-override"><code>OrderNumber   Quantity   Description
  ===========   ========   ====================
  STL-7442-1    7          prefabulated amulite
  MPT-9931-8A   32         spurving bearing
  KSG-0619-81   5          panametric fan
  KSG-0619-81   5          differential girdlespring
  </code></pre>
  
  <p>What I really want is to have SQL Server <em>just pick one</em>, as it will be <em>good enough</em>:</p>
  
  <pre class="lang-none prettyprint-override"><code>OrderNumber   Quantity   Description
  ===========   ========   ====================
  STL-7442-1    7          prefabulated amulite
  MPT-9931-8A   32         differential girdlespring
  KSG-0619-81   5          panametric fan
  </code></pre>
  
  <p>If I get adventurous, I might show the user, an ellipsis to indicate that there's more than one:</p>
  
  <pre class="lang-none prettyprint-override"><code>OrderNumber   Quantity   Description
  ===========   ========   ====================
  STL-7442-1    7          prefabulated amulite
  MPT-9931-8A   32         differential girdlespring
  KSG-0619-81   5          panametric fan, ...
  </code></pre>
  
  <p>So the question is how to either</p>
  
  <ul>
  <li>eliminate "duplicate" rows</li>
  <li>only join to one of the rows, to avoid duplication</li>
  </ul>
  
  <h2>First attempt</h2>
  
  <p>My first naive attempt was to only join to the "<strong>TOP 1</strong>" line items:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT Orders.OrderNumber, LineItems.Quantity, LineItems.Description
  FROM Orders
      INNER JOIN (
         SELECT TOP 1 LineItems.Quantity, LineItems.Description
         FROM LineItems
         WHERE LineItems.OrderID = Orders.OrderID) LineItems2
      ON 1=1
  </code></pre>
  
  <p>But that gives the error:</p>
  
  <blockquote>
    <p>The column or prefix 'Orders' does not<br>
    match with a table name or alias name<br>
    used in the query.  </p>
  </blockquote>
  
  <p>Presumably because the inner select doesn't see the outer table.</p>