Link to home
Start Free TrialLog in
Avatar of Butler Bros
Butler BrosFlag for United States of America

asked on

How to only return duplicates n this SQL query

I wish to return only those rows in which the 'counts' is > 1

  SELECT  alternate_code.alternate_code ,         
 inv_mast.item_id ,        
 alternate_code.inv_mast_uid,
 alternate_code.alternate_code_uid ,
COUNT(*) OVER (PARTITION BY  alternate_code.alternate_code ) counts 
 FROM alternate_code
    INNER JOIN inv_mast ON inv_mast.inv_mast_uid = alternate_code.inv_mast_uid
	Group by alternate_code.alternate_code, inv_mast.item_id,alternate_code.inv_mast_uid, alternate_code.alternate_code_uid 

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Please provide some context to your schema.  I am confused by the different grouping logic for "counts" and the GROUP BY.  Some sample data and desired output would help as well.

Lacking this the only thing I can suggest is adding a HAVING clause or wrapping the the query (or creating a CTE) in a select that uses a WHERE clause against "counts".

HAVING COUNT(*) > 1
Avatar of Butler Bros

ASKER

thanks fellas..
nice work
You don't need the GROUP BY.  If you decide you do want a DISTINCT, you can add it.

SELECT /*DISTINCT, if wanted / needed*/
    ac.alternate_code ,        
    im.item_id ,        
    ac.inv_mast_uid,
    ac.alternate_code_uid
FROM alternate_code ac
    INNER JOIN (
        SELECT alternate_code
        FROM alternate_code
        GROUP BY alternate_code
        HAVING COUNT(*) > 1
    ) AS ac_counts ON ac_counts.alternate_code = ac.alternate_code
    INNER JOIN inv_mast im ON im.inv_mast_uid = ac.inv_mast_uid