SQL Select - Group by along with inner join?

Question

I'm using the following query:

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

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.

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 :/

Any help much appreciated! :)

Solution:

;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

Answer 1

You can use ROW_NUMBER with a PARTITION BY clause to identify duplicates:

;WITH CTE AS (
   SELECT  ROW_NUMBER() OVER (PARTITION BY ITEMNUMBER 
                              ORDER BY ROWNUMBER DESC) AS rn,
           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 
)
SELECT TOP 12 *
FROM CTE 
WHERE rn = 1
ORDER BY ROWNUMBER DESC

Any record returned from CTE with rn > 1 is a duplicate and is thus filtered out by the WHERE clause. Duplicate records filtered out are the ones having the lowest ROWNUMBER values. You can change the ORDER BY clause of ROW_NUMBER to change this criterion any way you like.

Answer 2

You should use GROUP BY INVENTRANS.ITEMNUMBER (not in your inner join but in "main query") this will remove all dublicates of ITEMNUMBER.. DISINCT only removes completly identical sets.

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 

GROUP BY INVENTRANS.ITEMNUMBER

ORDER BY INVENTRANS.ROWNUMBER DESC