SQLTeam.com | Weblogs | Forums

How to find row(s) with the same value in a recordset


#1
Hi,

I got this query, that gives me all the products(optionid) that are ready to be shipped: 

SELECT T_Order_Detail.OrderID,
       T_Order_Detail.OptionID,
       T_Order_Detail.Quantity
FROM T_Product_Option
    INNER JOIN T_Order_Detail
        ON T_Product_Option.id = T_Order_Detail.OptionID
    LEFT OUTER JOIN T_Order_Main
        ON T_Order_Detail.OrderID = T_Order_Main.ORDERID
WHERE (T_Order_Main.Orderstatus = 7)
      AND (T_Product_Option.PackID = 3)
      AND (T_Order_Detail.Cost > 0)
ORDER BY T_Order_Detail.OrderID,
         OptionID;

it produces this result:
CREATE TABLE #temptable ( [OrderID] int, [OptionID] int, [Quantity] int )
INSERT INTO #temptable
VALUES
( 1689310, 4214, 1 ), 
( 1698321, 5497, 1 ), 
( 1698321, 5873, 2 ), 
( 1703147, 3933, 2 ), 
( 1708411, 5497, 1 ), 
( 1708500, 3415, 1 ), 
( 1709102, 6075, 1 ), 
( 1710479, 2903, 1 ), 
( 1710642, 3925, 1 ), 
( 1710885, 4214, 1 ), 
( 1710885, 4215, 1 ), 
( 1711090, 2905, 1 ), 
( 1711090, 4215, 1 ), 
( 1711766, 3383, 1 ), 
( 1711859, 3933, 1 ), 
( 1712026, 418, 1 ), 
( 1712626, 3913, 1 ), 
( 1712753, 3926, 1 ), 
( 1712924, 4215, 1 ), 
( 1713294, 4095, 1 ), 
( 1713294, 4213, 1 ), 
( 1713294, 6075, 1 ), 
( 1713442, 3414, 1 ), 
( 1713491, 3933, 2 ), 
( 1713491, 4248, 1 ), 
( 1713504, 3925, 1 ), 
( 1713504, 3933, 5 ), 
( 1713551, 2910, 2 ), 
( 1713551, 3414, 1 ), 
( 1713567, 5873, 1 ), 
( 1713579, 3385, 1 ), 
( 1713579, 3387, 2 ), 
( 1713615, 2905, 2 ), 
( 1713615, 5497, 2 ), 
( 1713621, 3933, 1 ), 
( 1713622, 6075, 2 ), 
( 1713624, 5497, 1 ), 
( 1713627, 3186, 1 ), 
( 1713640, 6075, 2 ), 
( 1713665, 3933, 2 ), 
( 1713670, 3383, 1 ), 
( 1713745, 418, 1 ), 
( 1713765, 2901, 2 ), 
( 1713765, 3183, 1 ), 
( 1713765, 4247, 2 ), 
( 1713773, 6155, 2 ), 
( 1713811, 2377, 1 ), 
( 1713811, 3382, 2 ), 
( 1713811, 3913, 1 ), 
( 1713821, 4248, 2 ), 
( 1713853, 3913, 1 ), 
( 1713866, 3383, 1 ), 
( 1713870, 2905, 1 ), 
( 1713870, 4214, 1 ), 
( 1713889, 3913, 1 ), 
( 1713890, 3933, 1 ), 
( 1713894, 3933, 1 ), 
( 1713896, 3933, 1 ), 
( 1713905, 2901, 1 ), 
( 1713905, 4214, 1 ), 
( 1713910, 3933, 1 ), 
( 1713926, 3383, 1 ), 
( 1713927, 2901, 1 ), 
( 1713938, 2901, 1 ), 
( 1713938, 4248, 1 ), 
( 1713941, 3383, 1 ), 
( 1713945, 3913, 1 ), 
( 1713946, 4095, 1 ), 
( 1713949, 3933, 1 ), 
( 1713955, 6155, 1 ), 
( 1713965, 4248, 1 ), 
( 1713978, 3913, 1 ), 
( 1713978, 4011, 1 ), 
( 1713980, 2901, 2 ), 
( 1713986, 2901, 1 ), 
( 1713986, 3933, 1 ), 
( 1713988, 3383, 1 ), 
( 1714012, 4247, 1 ), 
( 1714012, 4248, 1 ), 
( 1714023, 3383, 1 ), 
( 1714023, 5542, 1 ), 
( 1714026, 5497, 1 ), 
( 1714032, 2901, 1 ), 
( 1714034, 3383, 1 ), 
( 1714044, 2910, 1 ), 
( 1714054, 3913, 1 ), 
( 1714058, 5497, 1 ), 
( 1714064, 5873, 1 ), 
( 1714069, 5873, 1 ), 
( 1714070, 418, 1 ), 
( 1714073, 2901, 1 ), 
( 1714075, 6075, 1 ), 
( 1714082, 5497, 1 ), 
( 1714086, 4248, 1 ), 
( 1714086, 5873, 1 ), 
( 1714087, 3933, 6 ), 
( 1714099, 2910, 1 ), 
( 1714102, 4247, 1 ), 
( 1714109, 3933, 1 ), 
( 1714118, 5497, 1 ), 
( 1714130, 3383, 2 ), 
( 1714132, 6076, 1 ), 
( 1714146, 3913, 1 ), 
( 1714148, 4180, 1 ), 
( 1714158, 4247, 2 ), 
( 1714180, 3913, 1 ), 
( 1714188, 3933, 1 ), 
( 1714195, 1475, 2 ), 
( 1714197, 2901, 1 ), 
( 1714197, 4011, 1 ), 
( 1714197, 4247, 1 ), 
( 1714202, 2901, 1 ), 
( 1714217, 3933, 1 ), 
( 1714223, 5497, 1 ), 
( 1714226, 3933, 3 ), 
( 1714230, 3933, 1 ), 
( 1714244, 3925, 1 ), 
( 1714249, 3913, 1 ), 
( 1714257, 4248, 2 ), 
( 1714262, 4248, 2 ), 
( 1714263, 2901, 1 ), 
( 1714263, 4247, 1 ), 
( 1714270, 3417, 1 ), 
( 1714291, 3933, 2 ), 
( 1714292, 3933, 2 ), 
( 1714305, 2903, 1 ), 
( 1714305, 3913, 1 ), 
( 1714309, 3933, 1 ), 
( 1714314, 4247, 1 ), 
( 1714322, 6075, 1 ), 
( 1714332, 2901, 1 ), 
( 1714339, 3387, 1 ), 
( 1714346, 3933, 1 ), 
( 1714347, 3913, 1 ), 
( 1714365, 3933, 1 ), 
( 1714369, 3913, 1 ), 
( 1714369, 5497, 1 ), 
( 1714374, 5873, 1 ), 
( 1714380, 3382, 1 ), 
( 1714399, 3925, 1 ), 
( 1714400, 4248, 1 ), 
( 1714405, 5497, 1 ), 
( 1714412, 5578, 2 ), 
( 1714448, 5873, 1 ), 
( 1714454, 4247, 1 ), 
( 1714460, 5497, 1 ), 
( 1714461, 2903, 1 ), 
( 1714461, 6155, 1 ), 
( 1714470, 3933, 1 ), 
( 1714485, 5497, 2 ), 
( 1714499, 4248, 1 ), 
( 1714504, 2901, 1 ), 
( 1714504, 3933, 1 ), 
( 1714510, 3933, 2 ), 
( 1714521, 3933, 1 ), 
( 1714522, 4248, 1 ), 
( 1714535, 5497, 2 ), 
( 1714547, 1475, 1 ), 
( 1714560, 6075, 1 ), 
( 1714564, 3913, 1 ), 
( 1714564, 4247, 1 ), 
( 1714595, 2905, 1 ), 
( 1714599, 6155, 1 ), 
( 1714609, 3925, 1 ), 
( 1714613, 5542, 2 ), 
( 1714617, 4215, 1 ), 
( 1714624, 3926, 1 ), 
( 1714634, 2910, 1 ), 
( 1714634, 3933, 1 ), 
( 1714637, 2377, 1 ), 
( 1714637, 2901, 1 ), 
( 1714647, 2903, 2 ), 
( 1714660, 6155, 1 ), 
( 1714661, 3933, 1 ), 
( 1714664, 3913, 1 ), 
( 1714664, 4247, 1 ), 
( 1714699, 3913, 1 ), 
( 1714703, 3933, 2 ), 
( 1714712, 3913, 1 ), 
( 1714731, 4248, 1 ), 
( 1714735, 5497, 1 ), 
( 1714751, 4247, 1 ), 
( 1714758, 3926, 1 ), 
( 1714771, 3925, 1 ), 
( 1714776, 4095, 1 ), 
( 1714776, 4214, 1 ), 
( 1714776, 5497, 1 ), 
( 1714776, 5873, 1 ), 
( 1714792, 2901, 1 ), 
( 1714797, 3933, 1 ), 
( 1714802, 3925, 1 ), 
( 1714807, 2901, 1 ), 
( 1714842, 927, 1 ), 
( 1714843, 5873, 1 ), 
( 1714848, 3933, 1 ), 
( 1714848, 4247, 1 ), 
( 1714849, 3933, 1 ), 
( 1714849, 4011, 1 ), 
( 1714849, 4212, 1 ), 
( 1714855, 3913, 2 ), 
( 1714861, 3913, 1 ), 
( 1714872, 3913, 1 ), 
( 1714877, 3933, 1 ), 
( 1714892, 3913, 2 ), 
( 1714894, 3913, 1 ), 
( 1714914, 5497, 1 ), 
( 1714931, 3913, 1 ), 
( 1714934, 3933, 1 ), 
( 1714940, 3933, 1 ), 
( 1714954, 4247, 1 ), 
( 1714958, 2901, 1 ), 
( 1714958, 4247, 1 ), 
( 1714967, 2903, 1 ), 
( 1714974, 4247, 2 ), 
( 1714983, 5497, 1 ), 
( 1714986, 4248, 1 ), 
( 1714991, 3913, 1 ), 
( 1714999, 3925, 1 ), 
( 1715003, 3180, 1 ), 
( 1715004, 2901, 2 ), 
( 1715008, 4247, 1 ), 
( 1715009, 4248, 1 ), 
( 1715036, 3930, 1 ), 
( 1715037, 3933, 1 ), 
( 1715039, 5497, 1 ), 
( 1715044, 3926, 1 ), 
( 1715053, 3386, 1 ), 
( 1715054, 4247, 1 ), 
( 1715064, 2905, 1 ), 
( 1715064, 5497, 1 ), 
( 1715068, 3387, 2 ), 
( 1715070, 2905, 1 ), 
( 1715070, 6155, 2 ), 
( 1715122, 6075, 1 ), 
( 1715133, 2903, 1 ), 
( 1715133, 3925, 1 ), 
( 1715137, 3933, 1 ), 
( 1715145, 3387, 1 ), 
( 1715160, 3933, 1 ), 
( 1715165, 926, 1 ), 
( 1715167, 3933, 1 ), 
( 1715170, 5542, 1 ), 
( 1715170, 5578, 2 ), 
( 1715177, 3913, 1 ), 
( 1715177, 4247, 1 )

DROP TABLE #temptable

I want to find the amount of orders that have the exactly the same contents.
Breaking my head on this for a while.
The result Im looking for is the OptionID, Quantity have to be the same keeping in mind order can from 1 row to N:

Something that looks like this
ex:
Amount_of_Similiar_Orders, OptionID, Quantity

10      ,  4214  ,   1
7        ,  3383  ,   2
5        ,  418    ,   1
3        ,  2901  ,   1
3        ,  2903  ,   1
.....

the first 3 rows are orders with only 1 item.
Last 2 rows represent 3 orders containing 2 items

there must be an easy simple way to do this, with a Pivot or something, that beyond my DB knowledge

Any help is very much appreciated

SQL server 2014


#2

Try this:

select a.orderid
      ,a.optionid
      ,a.quantity
  from (select a.orderid
              ,b.optionid
              ,b.quantity
              ,count(*) over(partition by b.optionid,b.quantity) as cnt
          from t_order_main as a
               inner join t_order_detail as b
                       on b.orderid=a.orderid
                      and b.cost>0
               inner join t_product_option as c
                       on c.optionid=a.optionid
                      and c.packid=3
         where a.orderstatus=7
       ) as a
 where a.cnt>1
;

#3

Hi Bitsmed,

something not correct, here is a Image with all the fields in the tables


#4

Try this then:

select a.orderid
      ,a.optionid
      ,a.quantity
  from (select a.orderid
              ,b.optionid
              ,b.quantity
              ,count(*) over(partition by b.optionid,b.quantity) as cnt
          from t_order_main as a
               inner join t_order_detail as b
                       on b.orderid=a.orderid
                      and b.cost>0
               inner join t_product_option as c
                       on c.id=a.optionid
                      and c.packid=3
         where a.orderstatus=7
       ) as a
 where a.cnt>1
;