Remove duplicates from a dataset already with duplicates-CodePudding

I have the following dataset, what I need to do is to be able to extract only ItemCode with Different LotNo

RecCnt Store DelNo      DelDate     TruckNo   ItemCode   LotNo       Line
2      443  B10237819   20220108    ZZZ023  10072879    0005113056  1
2      443  B10237819   20220108    ZZZ023  10072879    0005113430  2
2      443  B10237819   20220108    ZZZ023  10078415    0005109945  3
2      443  B10237819   20220108    ZZZ023  10078415    0005109945  4
2      443  B10237819   20220108    ZZZ023  10083294    0005113578  5
2      443  B10237819   20220108    ZZZ023  10083294    0005113578  6
2      443  B10237819   20220108    ZZZ023  10087629    0005108688  7
2      443  B10237819   20220108    ZZZ023  10087629    0005108688  8

From the above, only lines 1 and 2 should be returned. This is my query, I'm kinda stuck..

SELECT RecCnt , DT.Store, DT.DelNo, DT.DelDate, DT.TruckNo, DT.Item, 
    MBT.Lot 
FROM
(
    SELECT COUNT (LOT) AS RecCnt, Store, DelNo, DelDate, TruckNo, Item
    FROM MWTS_TMD
    WHERE DelNo = 'B10237819'
    HAVING COUNT(ITEM) > 1 
    GROUP BY Store, Item, DelNo, DelDate, TruckNo, Item
) DT
LEFT JOIN MW_BTS_TMD MBT
    ON MBT.Store = DT.Store
        AND MBT.DelNo = DT.DelNo
        AND MBT.Item = DT.Item

CodePudding user response：

If I understand correctly, you can try to use DENSE_RANK window function, DENSE_RANK will assign ranks to rows in partitions with no gaps in the ranking values.

T is the result set from your sample data

Query 1:

SELECT *
FROM (
  SELECT *,DENSE_RANK() OVER(ORDER BY ItemCode) rn
  FROM T 
) t1
WHERE rn = 1

Results:

| RECCNT | STORE |     DELNO |  DELDATE | TRUCKNO | ITEMCODE |      LOTNO | RN |
|--------|-------|-----------|----------|---------|----------|------------|----|
|      2 |   443 | B10237819 | 20220108 |  ZZZ023 | 10072879 | 0005113056 |  1 |
|      2 |   443 | B10237819 | 20220108 |  ZZZ023 | 10072879 | 0005113430 |  1 |

CodePudding user response：

You could use a subquery:

SELECT * 
FROM MWTS_TMD
WHERE Id IN (
    SELECT Id
    FROM Announcement 
    GROUP BY LotNo
    HAVING COUNT(*) = 1);

CodePudding user response：

You can use a subquery to get the ItemCode that has more than one different LotNo by using the HAVING clause on the LotNo column (this would be instead of your FROM() part:

WHERE ItemCode IN
(SELECT ItemCode 
 FROM  MWTS_TMD 
 GROUP BY ItemCode, LotNo
 HAVING COUNT(LotNo) > 1)