RGS RGS - 2 months ago 8
SQL Question

How to get duplicate rows inclusive of first row number in SQL Server?

I have written below query to retrieve duplicate customers using Row_Number() in SQL Server.

Cust_PKID ---------------+ CustomerID ----------------- + MobileNo
1 | A00001 | 9000477444
2 | A00002 | 9000477444
3 | A00003 | 9000477444


Query:-

Select TMP.CustID
From
(
Select CustomerID CustID,
Row_Number() Over(Partition By MobileNo Order By (Select Null)) As RowNo
From dbo.Customers
) TMP
Where TMP.RowNo > 1


Output:-

Cust_PKID ---------------+ CustomerID ----------------- + MobileNo
2 | A00002 | 9000477444
3 | A00003 | 9000477444


How can I retrieve records including of first RowNo record in single select statement?

Answer

You are looking for COUNT() OVER() window function not ROW_NUMBER

Select TMP.CustID
From
(
   Select CustomerID CustID,
       COUNT(1) Over(Partition By MobileNo) As RowNo
   From dbo.Customers
) TMP
Where TMP.RowNo > 1

This will bring all the duplicate MobileNo records

Comments