Database

David Mujica wrote:

Use this statement to verify what you think happened ...

David Mujica wrote:

select name,default_database_name,modify_date from sys.sql_logins

select [colNames]
from tableName
group by [colNames]
having count(*) > 1

select [ID,colNames,Phone,...]from tableNamegroup by [colNames]having count(*) > 1

SELECT	ID, otherfields FROM table
GROUP BY otherfields 
HAVING COUNT(*) > 1

;WITH SalesCTE(Product, SaleDate, SalePrice, Ranking)

AS

(

SELECT

Product, SaleDate, SalePrice,

Ranking = DENSE_RANK() OVER(PARTITION BY Product, SaleDate, SalePrice ORDER BY NEWID() ASC)

FROM SalesHistory

)

DELETE FROM SalesCTE

WHERE Ranking > 1

CREATE TABLE #tmpData
(
	id INT IDENTITY(1,1),
	custname VARCHAR(MAX)
)

INSERT INTO #tmpData
SELECT m.Customername 
FROM mytable1 m 
GROUP BY m.Customername,m.phonenumber,m.city,m.street HAVING COUNT(m.Customername)>1

DECLARE @i AS INT
DECLARE @query AS nvarCHAR(MAX)

SET @i=0
SET @query = 'select * from mytable1 where customername in ('

WHILE(@i<(SELECT COUNT(*) FROM #tmpData))
BEGIN
	SET @i=@i+1
	SET @query=@query+(SELECT char(39)+custname + char(39) FROM #tmpData WHERE id=@i)+',' 
	
END
SET @query=SUBSTRING(@query,1,LEN(@query)-1)
SET @query=@query+')'

EXEC(  @query)
DROP TABLE #tmpData

select a.id,a.Customername , a.phonenumber, a.city, a.street     
from mytable1 a
inner join 
(select Customername , phonenumber, city, street
from mytable1
group by customername,phonenumber,city,street
having count(*) > 1) b  
on a.Customername = b.Customername

select * from myTable where customername in (
select customername
from myTable 
group by customername,phonenum,city,street
having(count(customername)>1 and count(phonenum)>1 and count(city)>1 and count(street)>1))

id	customername	phonenum	city	street
19	jon	555	NK	st.5
37	jon	555	NK	st.5