SELECT of all the fields of records with duplicated fields?

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.

Answer 1

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

Answer 2

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
	)