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:

I would like to return:

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:

CodePudding user response：

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;