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