Removing duplicate groups

Other SQL Server Topics
1

I have a temp table that contains duplicate groups. The groups are actually 2 tiered. For example, a group is a package with multiple products in that package. Below is a snippet showing a duplicate package. I indented the product to make it easier to read. Is there a way to only show one group? Maybe this is something easy and I'm just overthinking it. Any help would be greatly appreciated!!! :smile:
THANKS!!!

As a side note, i tried using distinct however it changed everything around. Like placing products above the package or in the wrong package. Basically distinct didn't seem to help.

** svctypeid TierType**

1511    Package
1214        Product
1217        Product
1493        Product 
1511    Package
1214        Product
1217        Product
1493        Product
2

Use the same ORDER BY clause in your query that uses DISTINCT as the one you used to produce this sample output.

3

Hi James,

Thanks for your reply! I tried your suggestion but had unexpected results which might be my fault as I wanted to simplify things. Ill go back to the start on this one :smile:

My original code to pull in the data into the temp table was:

 select distinct nus.SvcID, nus.ParentSvcID, pc.svctypeid, pc.svcdesc,  pc.charge
 , case when producttype = 2 then 'Package' else 'Product' end as 'Type'
from tbl1 nus join tbl2 pc on nus.svctypeid = pc.svctypeid
group by nus.SvcID, nus.ParentSvcID, pc.svctypeid, pc.svcdesc, pc.charge, producttype
order by nus.SvcID, nus.ParentSvcID, pc.svctypeid, pc.svcdesc, pc.charge

This gave me the below results:

SvcID Parent  typeid   svcdesc  charge    TYPE
1000    NULL   1517   svcdesc    NULL    Package
1001    1000    1511    svcdesc    NULL    Package
1002    1001    1214    svcdesc    NULL    Product
1003    1001    1217    svcdesc    NULL    Product
1004    1001    1493    svcdesc    NULL    Product
1005    1000    1779    svcdesc    NULL    Package
1006    1005    1215    svcdesc    NULL    Product
1007    1005    1216    svcdesc    NULL    Product
1008    1005    1559    svcdesc    NULL    Product
1009    1005    1893   svcdesc    NULL    Product
1010    1005    1894    svcdesc    NULL    Product
1011    NULL    2685    svcdesc    NULL    Package
1012    1011    5629    svcdesc    NULL    Product
1013    1011    5680    svcdesc    NULL    Product
1014    NULL    1517    svcdesc   NULL    Package
1015    1014    1511    svcdesc    NULL    Package
1016    1015    1214    svcdesc    NULL    Product
1017    1015    1217    svcdesc    NULL    Product
1018    1015    1493    svcdesc    NULL    Product
1019    1014    1779    svcdesc    NULL    Package
1020    1019    1215    svcdesc    NULL    Product
1021    1019    1216    svcdesc    NULL    Product
1022    1019    1559    svcdesc    NULL    Product
1023    1019    1893    svcdesc    NULL    Product
1024    1019    1894    svcdesc    NULL    Product
1025    NULL    2685    svcdesc    NULL   Package
1026    1025    5629    svcdesc    NULL    Product
1027    1025    5680    svcdesc    NULL    Product

As you can see, the SvcID is incremental which would render distinct useless (i think). You'll notice that the top tier package contains another package with the products underneath. This is one of the places I tried to simplify in my original example. I tried a number of things with the group by and order by but didn't have any luck so I decided to try inserting the data into a temp table and remove the svcID and Parent ID columns hoping that would help. This is why I had less columns in my original example.

I hope this helps and makes some sense. If not, ill provide whatever other information you need or clarification. I think Ive included everything in this post. Thoughts?

4

it appears that the ParentSvcID is NULL for the Highest tier Package, then the package contained within the orig package (if there is one) has its own ParentSvcID, and any Products below that are all the same ParentSvcID. So I'm guessing there has to be some group by or order by I'm not getting right. Ive moved things around in the group by and order by but haven't had any luck yet.