In my last post, I showed how to find duplicate entries in a sql server table using group by which was pretty simple to pull off. I thought it would be a good idea to find the list of all the duplicate rows using CTE for those that are fan of it. We will be using the same table from our previous post on finding duplicate rows which you can read and follow.

What is CTE

A CTE which is short for Common Table Expression, is a temporary result set that you can reference within another query statement. They are used to simplify large queries.

Find duplicate rows CTE

   WITH student_cte AS(
        SELECT  First_Name, 
                Last_Name,
                Phone,  
                COUNT(*) Occurrences
        FROM dbo.students
        GROUP BY
                First_Name, 
                Last_Name,
	        Phone
       HAVING COUNT(*) > 1
  )
  SELECT	st.Id,
		st.First_Name, 
		st.Last_Name,
		st.Phone  		
 FROM dbo.students st
 INNER JOIN student_cte 
  ON student_cte.First_Name = st.First_Name
  AND student_cte.Last_Name = st.Last_Name;

Find duplicate entries result