title: How to distinct records in Dynamics X++?
body: <p>I need to remove repeated rows from my query, but I can't get the expected result. I researched I found out I should use <code>GROUP BY</code> but if I do use that I don't get the results I was getting before. Is this the correct way to distinct records in X++? By the way, I'm using 6 tables, here's my X++ Query:</p>
  
  <pre><code>SELECT RecId, City, BuildingCompliment
      FROM %1 GROUP BY RecId JOIN Location FROM %2
          OUTER JOIN StateId, Name FROM %3
          OUTER JOIN ISOcode FROM %4
          OUTER JOIN CurrencyCode, Symbol, Txt FROM %5
          OUTER JOIN ShortName FROM %6
      WHERE (%1.ValidTo == DateTimeUtil::maxValue() || %1.ValidTo &gt; DateTimeUtil::utcNow())
          &amp;&amp; %2.Location == %1.Location
          &amp;&amp; %3.CountryRegionId == %2.CountryRegionId &amp;&amp; %3.StateId == %2.State
          &amp;&amp; %4.CountryRegionId == %1.CountryRegionId
          &amp;&amp; %5.CurrencyCode == %4.CurrencyCode
          &amp;&amp; %6.LanguageId == 'EN-US' &amp;&amp; %6.CountryRegionId == %1.CountryRegionId
  </code></pre>
  
  <p>Here's the result with <code>GROUP BY RecId</code>:</p>
  
  <p><code>RecId - Location - StateName</code></p>
  
  <pre><code>5637145326 - 0 - 
  5637145327 - 0 - 
  5637145328 - 0 - 
  5637146076 - 0 - 
  5637146077 - 0 - 
  5637150576 - 0 - 
  5637150577 - 0 - 
  5637150578 - 0 - 
  </code></pre>
  
  <p>Here's without <code>GROUP BY RecId</code>:</p>
  
  <pre><code>5637150578 - 5637145326 - New York
  ...
  5637150578 - 5637145326 - New York
  </code></pre>
  
  <p>All repeated. Any ideas on what am I doing wrong? Thanks!</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>Tables/Views used:</p>
  
  <pre><code>DirPartyPostalAddressView //%1
  LogisticsPostalAddress //%2
  LogisticsAddressState //%3
  LogisticsAddressCountryRegion //%4
  Currency //%5
  LogisticsAddressCountryRegionTranslation //%6
  </code></pre>
  
  <p>Edited query:</p>
  
  <pre><code>string sqlAxCommand = @"SELECT RecId, City, BuildingCompliment, Location, CountryRegionId FROM %2
                                          GROUP BY RecId, City, BuildingCompliment, Location, CountryRegionId
                                      OUTER JOIN StateId, Name FROM %3
                                          WHERE %3.CountryRegionId == %2.CountryRegionId 
                                             &amp;&amp; %3.StateId         == %2.State
                                      OUTER JOIN ISOcode FROM %4
                                          WHERE %4.CountryRegionId == %2.CountryRegionId
                                      OUTER JOIN CurrencyCode, Symbol, Txt FROM %5
                                          WHERE %5.CurrencyCode == %4.CurrencyCode
                                      OUTER JOIN ShortName FROM %6
                                          WHERE %6.CountryRegionId  == %2.CountryRegionId
                                             &amp;&amp; %6.LanguageId       == 'EN-US'
                                      EXISTS JOIN %1
                                          WHERE %1.Location         == %2.Location
                                             &amp;&amp; %1.IsPostalAddress  == NoYes::Yes";
  </code></pre>