Find Duplicates in a SQL table

Transact-SQL
1

My table contains multiple lots (LOT_ID) and each lot contains multiple products(PRODUCT_ID) and there are multiple orders (ORDER_ID) under each Product. I would like to know the order ID’s which are repeated for multiple products for a given LOT

Table:

Result

S.NO LOT_ID Product_ID Order_ID
1 101 P108 90001
2 101 P109 90001
3 102 S189 10098
4 102 S465 10098

Order ID's ( 90001 & 10098 ) repeated twice for multiple products under the same LOT. so result set should only display the LOT_ID, PRODUCT_ID related to these orders

2 
select b.*     /* specify which fields you want to see */
  from (select lot_id
              ,order_id
          from yourtable
         group by lot_id
                 ,order_id
         having count(*)>1
       ) as a
       inner join yourtable as b
               on b.lot_id=a.lot_id
              and b.order_id=a.order_id
3

Above query is displaying all the rows from the Parent table which is wrong.

It should display the rows in which Order ID's repeated for multiple products, please check my above example.

4

The query I wrote, gives this result:

s.no  lot_id  product_id  order_id
1     101     P108        90001
2     101     P109        90001
4     102     S189        10098
6     102     S465        10098

The only thing that different, is the 3rd and 4th s.no, which in my case is 4 and 6, in your case is 3 and 4.
Now if you look at your own sample data where lot_id=102 and order_id=10098, the s.no=4 and 6, so either your sample data is worng or your last comment are.

5

you are right, sample table did not contain all the rows. I just updated the original table, can you please verify it now

Thanks

6

Where did the column s.no go?

Result:

lot_id  product_id  order_id
101     P110        80900
101     P110        80900
101     P108        90001
101     P108        90001
101     P109        90001
102     S189        10098
102     S465        10098
102     S465        10098
103     L109        20090
103     L109        20090