SQLTeam.com | Weblogs | Forums

Insert Row Where only 1 Row Exists for a Grouping

Hi experts,
I need to collect rows where there is only 1 row for a grouping of (Column1 + Column2 + Column3)
I will insert this result-set into a new table.
Can anyone help me with the TSQL code?
Thanks.


;WITH find_single_rows AS (
    SELECT Column1, Column2, Column3
    FROM dbo.table_name
    GROUP BY Column1, Column2, Column3
    HAVING COUNT(*) = 1
)
--INSERT INTO dbo.new_table ( ... )
SELECT tn.*
FROM find_single_rows fsr
INNER JOIN dbo.table_name tn ON tn.Column1 = fsr.Column1 AND 
    tn.Column2 = fsr.Column2 AND tn.Column3 = fsr.Column3
1 Like

Thanks @ScottPletcher. I used a different technique which gives almost the same results:
select t1.anbrID,t1.intShipNbr,t1.strFileName,t1.Title,t1.intPageNbr,t1.intTotalPages,t1.intFileSizeInBytes,t1.booDeliveryReceipt
,t1.booBOL,t1.dteProcessed,t1.strProNbr,t1.strSCAC
from tblImageFiles t1
where t1.strSCAC IN ('CNWY', 'CTII')
and strProNbr in (select strProNbr from tblImageFiles group by strProNbr having count(*) = 1)