locked
SELECT of all the fields of records with duplicated fields? RRS feed

  • Question

  • I have a table [TRANS] that has the following fields: ID, REQ_ID_TRANS, REQ_ID, CREATE_DATE, and a few other fields.
    I need to be able to retrieve all the records (with all its fields) that have duplicate values in REQ_ID_TRANS, REQ_ID.
    I can easily find the duplicate records by using a SELECT of those two fields and using GROUP BY & HAVING COUNT(*) > 1. That's not the question.
    My question is: Without using a cursor, a temp table, or a nested SELECT, how can I integrate this GROUP BY into a SELECT that will display all the fields from all those duplicate records?

    Essentially, I will display all the fields of all the records that have REQ_ID_TRANS, REQ_ID duplicated.
    Thanks.
    Friday, December 2, 2011 7:04 PM

Answers

  • If you're using SQL 2005 and up:

    ;with cte as (select *, count(*) over (partition by REQ_ID_TRANS, REQ_ID) as Cnt from [Trans])
    
    select * from cte where Cnt > 1
    

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Proposed as answer by Hasham NiazEditor Saturday, December 3, 2011 9:41 PM
    • Marked as answer by Kalman Toth Friday, December 9, 2011 7:11 AM
    Friday, December 2, 2011 7:10 PM

All replies

  • If you're using SQL 2005 and up:

    ;with cte as (select *, count(*) over (partition by REQ_ID_TRANS, REQ_ID) as Cnt from [Trans])
    
    select * from cte where Cnt > 1
    

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Proposed as answer by Hasham NiazEditor Saturday, December 3, 2011 9:41 PM
    • Marked as answer by Kalman Toth Friday, December 9, 2011 7:11 AM
    Friday, December 2, 2011 7:10 PM
  • You could use the below (that is based analytic function rather than group by) and returns the rows that have duplicates (including all the occurrences of it). I mean if you have two records with same Req_ID_Trans and Req_ID combination, you would get both of those records.

    ;WITH CTE AS
    (
    	SELECT	*
    	,		ROW_NUMBER() OVER(PARTITION BY REQ_ID_TRANS, REQ_ID ORDER BY ID) AS Ranking
    )
    SELECT	*
    FROM	CTE	CTEOuter
    WHERE	EXISTS
    	(
    		SELECT	1
    		FROM	CTE	CTEInner
    		WHERE	CTEOuter.REQ_ID_TRANS = CTEInner.REQ_ID_TRANS
    		AND		CTEOuter.REQ_ID = CTEInner.REQ_ID 
    		AND		CTEInner.Ranking > 1
    	)
    

    • Proposed as answer by Sanjeewan Kumar Friday, December 2, 2011 7:16 PM
    • Unproposed as answer by Naomi N Friday, December 2, 2011 7:17 PM
    Friday, December 2, 2011 7:15 PM