title: SQL query to return one single record for each unique value in a column
body: <p>I have a table in SQL Server 2000 that I am trying to query in a specific way.  The best way to show this is with example data.</p>
  
  <p>Behold, <code>[Addresses]</code>:</p>
  
  <pre><code>Name         Street                 City          State
  --------------------------------------------------------
  Bob          123 Fake Street        Peoria        IL
  Bob          234 Other Street       Fargo         ND
  Jim          345 Main Street        St Louis      MO
  </code></pre>
  
  <p>This is actually a simplified example of the structure of the actual table.  The structure of the table is completely beyond my control.  I need a query that will return a single address per name.  It doesn't matter which address, just that there is only one.  The result could be this:</p>
  
  <pre><code>Name         Street                 City          State
  --------------------------------------------------------
  Bob          123 Fake Street        Peoria        IL
  Jim          345 Main Street        St Louis      MO
  </code></pre>
  
  <p>I found a similar question <a href="https://stackoverflow.com/questions/715804/query-to-return-1-instance-of-a-record-with-duplicates">here</a>, but none of the solutions given work in my case because I do not have access to <code>CROSS APPLY</code>, and calling <code>MIN()</code> on each column will mix different addresses together, and although I don't care which record is returned, it must be one intact row, not a mix of different rows.</p>
  
  <p>Recommendations to change the table structure will not help me.  I agree that this table is terrible, (it's worse than shown here) but this is part of a major ERP database that I can not change.</p>
  
  <p>There are about 3000 records in this table.  There is no primary key.</p>
  
  <p>Any ideas?</p>