title: SQL - Associate one record from a table to the ones of another only one time
body: <p>I have this 2 tables:
  Position/Article</p>
  
  <pre><code>+--------------+---------------+------------------+---------+
  | Col Position | Col IdArticle | Col ExpiringDate | Col xxx |
  +--------------+---------------+------------------+---------+
  |         1001 |           123 | 31/12/2018       | abc     |
  |         1002 |           123 | 31/11/2018       | cdf     |
  |         1003 |           123 | 31/12/2019       | ghi     |
  |         2001 |           456 | 31/12/2018       | lko     |
  |         2002 |           456 | 31/01/2018       | oki     |
  |         2003 |           456 | 12/12/2019       | oono    |
  +--------------+---------------+------------------+---------+
  </code></pre>
  
  <p>CustomerOrder/Article</p>
  
  <pre><code>+-------------------+---------------+
  | Col CustomerOrder | Col IdArticle |
  +-------------------+---------------+
  | A                 |           123 |
  | A                 |           456 |
  | B                 |           123 |
  | B                 |           456 |
  | C                 |           123 |
  | C                 |           456 |
  +-------------------+---------------+
  </code></pre>
  
  <p>I want to get, for each <code>CustomerOrder</code> and <code>IdArticle</code> one position for the <code>IdArticle</code> (each position must not be repeated) <em>and extra columns from the table Position/Article</em></p>
  
  <p>This is the result I would like to have:</p>
  
  <pre><code>+--------------+---------------+--------------+------------------+---------+
  | Col Customer | Col IdArticle | Col Position | Col ExpiringDate | Col xxx |
  +--------------+---------------+--------------+------------------+---------+
  | A            |           123 |         4001 | xxxx             | aaaa    |
  | A            |           456 |         2001 | aaaa             | kkkkk   |
  | B            |           123 |         1002 | bbbbb            | loojj   |
  | B            |           456 |         2002 | cccc             | jokjio  |
  | C            |           123 |         1003 | dddd             | hijooi  |
  | C            |           456 |         2003 | eeee             | ookii   |
  +--------------+---------------+--------------+------------------+---------+
  </code></pre>
  
  <p>I've tried with joins and <code>row_number</code> but was not able to come to a solution; it's probably much more easier than I think.</p>