title: SQL Select - Group by along with inner join?
body: <p>I'm using the following query:</p>
  
  <pre><code>SELECT TOP 12 
          INVENTABLE.ITEMNUMBER, 
          INVENTABLE.ITEMNAME1, 
          INVENTABLE.ITEMNAME2, 
          INVENTABLE.W_TILBUD, 
          INVENTABLE.COSTPRICE, 
          INVENTABLE.VENDITEMNUMBER, 
          INVENTABLE.A_PRODUCENT, 
          INVENTABLE.GROUP_, 
          INVENTABLE.A_GROSSISTLAGER, 
          INVENTABLE.SupplementaryUnits 
  
      FROM INVENTRANS
  
      INNER JOIN INVENTABLE ON INVENTABLE.ITEMNUMBER=INVENTRANS.ITEMNUMBER 
  
      WHERE   INVENTRANS.ACCOUNT='xxx'    AND 
              INVENTABLE.W_VISFORSIDE = 0 AND 
              INVENTABLE.W_VISWWW = 1     AND 
              INVENTABLE.BLOCKED = 0 
  
      ORDER BY INVENTRANS.ROWNUMBER DESC
  </code></pre>
  
  <p>This query selects the 12 last sold items from one table (INVENTRANS), and gets the item information from another (INVENTABLE). It works like it should, except I would like to prevent multiple of the same item (INVENTRANS.ITEMNUMBER) to be in the same result.</p>
  
  <p>I have tried looking around for solutions and tried several examples with a DISTINCT or GROUP BY, but I haven't been able to make it work :/</p>
  
  <p>Any help much appreciated! :)</p>
  
  <p><strong>Solution:</strong></p>
  
  <pre><code>;WITH CTE AS (
         SELECT  ROW_NUMBER() OVER (PARTITION BY INVENTRANS.ITEMNUMBER 
                                   ORDER BY INVENTRANS.ROWNUMBER DESC) AS rn,
             INVENTRANS.ITEMNUMBER,
             INVENTRANS.ROWNUMBER,
             INVENTABLE.ITEMNAME1, 
             INVENTABLE.ITEMNAME2, 
             INVENTABLE.W_TILBUD, 
             INVENTABLE.COSTPRICE, 
             INVENTABLE.VENDITEMNUMBER, 
             INVENTABLE.A_PRODUCENT, 
             INVENTABLE.GROUP_, 
             INVENTABLE.A_GROSSISTLAGER, 
             INVENTABLE.SupplementaryUnits 
      FROM INVENTRANS
      INNER JOIN INVENTABLE ON INVENTABLE.ITEMNUMBER=INVENTRANS.ITEMNUMBER 
      WHERE   INVENTRANS.ACCOUNT='xxx'    AND 
              INVENTABLE.W_VISFORSIDE = 0 AND 
              INVENTABLE.W_VISWWW = 1     AND 
              INVENTABLE.BLOCKED = 0 
  )
  SELECT TOP 12 *
  FROM CTE 
  WHERE rn = 1
  ORDER BY ROWNUMBER DESC
  </code></pre>