1

I have a table containing Services (service_id) for different Packages(package_id)

I am trying to find Packages with the exact same Services and keep only one Package of them

dummy_id    package_id  service_id
    4           4           3
    5           4           4
    6           4           5
    7           5           3
    8           5           4
    9           5           5
    10          6           3
    12          6           5
    13          7           3
    14          7           4
    15          7           5
    16          8           3
    17          8           4
    18          8           5
    19          9           3
    20          9           4
    21          9           5
    22          9           6
    23          9           7
    24          9           8
    25          10          3
    26          10          4
    27          10          5

I have an SQL Fiddle example: http://sqlfiddle.com/#!18/a07d1/3/23

In the example, PackageIDs 6 and 9 do not have the same Services. The other Packages have the same Services so one is kept (PackageID 4)

dummy_id    package_id  service_id
    4           4           3
    5           4           4
    6           4           5
    10          6           3
    12          6           5
    19          9           3
    20          9           4
    21          9           5
    22          9           6
    23          9           7
    24          9           8

Thank you

4
  • 1
    The problem is how do you define th the same Services, why is it 3,4,5 and not 3,4 , or 5 , or.... etc ? If the the same Services pattern is known it's much easier , a simple having count would do the trick Commented Jan 3, 2023 at 14:45
  • Hi Ergest. Package_ids that have the very same Services_ids must left to only one. PackageID 9 has more Services although it includes the Services of PackageID 4. It is different. PackageID 6 is missing ServiceID 4 so it is different. PackageIDs 4,5,7,8,10 has the very same ServiceIDs.
    – PanosPlat
    Commented Jan 3, 2023 at 14:49
  • I'm assuming the service_id is unique within each package_id? (I.e. the same service_id can't exist multiple times for the same package_id.)
    – J.D.
    Commented Jan 3, 2023 at 14:57
  • Hi J.D. Yeap. That is correct. We cannot have the same Service more than once inside a Package
    – PanosPlat
    Commented Jan 3, 2023 at 16:10

1 Answer 1

1

All the following steps can be found in this working fiddle

First we find the package_id with same values with the following query,

select  package_id,
        rank() over (order by count(*)) as rnk
from dbo.package_components 
group by  package_id;

Then we find the min package_id for the most repeated same values and the remaining package_id (which in your case have the different values),

select min(package_id) as min_package_id ,
       count(rnk) as rnk
from (  select  package_id,
                rank() over (order by count(*)) as rnk
        from dbo.package_components 
        group by  package_id
      ) tbl 
group by rnk 

In the end we use above query as a subquery with the main table and will get the desired result:

select pc.dummy_id,
       pc.package_id,
       pc.service_id
from dbo.package_components  pc
inner join (select min(package_id) as min_package_id ,
                  count(rnk) as rnk
            from (  select  package_id,
                            rank() over (order by count(*)) as rnk
                    from dbo.package_components 
                    group by  package_id
                 ) tbl 
           group by rnk  
          ) as x on x.min_package_id=pc.package_id
order by pc.dummy_id asc;

Maybe not the best solution but I guess will give the desired result.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.