Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Finding Duplicates in a single column and return all other column data

I have a table with data that I would like to find any rows with a duplication Position # (one of my columns in the data).
I have written some code that is working but it does not allow me to see the additional Column/header information.

Data Table:

MainItem BomLevel Position ComponentItem CompDesc TotalQty
316006 1 10 500006 Conv Kit 1
316006 1 20 562060 Battery 4
316006 1 30 VS147 Charger 1
316006 1 40 9970 Red Pad 1
316006 1 60 563844 Blue Pad 1
316006 1 60 512346 Machine 1

I would like to return:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

MainItem BomLevel Position ComponentItem CompDesc TotalQty
316006 1 60 563844 Blue Pad 1
316006 1 60 512346 Machine 1

This is the code I currently know how to write:

select    
    a.MainItem
    , a.BomLevel
    , a.Position    
from reports.v_bom a    
where a.MainItem = '316006'    
group by a.MainItem, a.BomLevel, a.Position    
having Count (*) > 1 

but this will only return:

MainItem BomLevel Position
316006 1 60

>Solution :

As you’ve only tagged SQL the following is ANSI SQL and will work in most modern RDBMS that support analytic window functions:

with c as (
    select *, Count(*) over(partition by mainitem, bomlevel, position) cnt
    from t
)
select * 
from c
where cnt > 1;
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading