How to findout this criteria [Resolved]

Posted by Jayakumars under C# on 3/30/2017 | Points: 10 | Views : 231 | Status : [Member] [MVP] | Replies : 1
--create table temp1
--(
--FileID int,
--Filepath nvarchar(255)
--)

--Insert into temp1 values(1,'\\aa\\bb\1.jpg')
--Insert into temp1 values(2,'\\aa\\bb\2.jpg')
--Insert into temp1 values(3,'\\aa\\bb\3.jpg')
--Insert into temp1 values(4,'\\aa\\bb\4.jpg')
--Insert into temp1 values(5,'\\aa\\bb\3.jpg')
--Insert into temp1 values(6,'\\aa\\bb\2.jpg')


select * from temp1

-- I need find Duplicate record for this criteria

--My Output this becos Fileid based i created path so i need duplicate entry shows here

--FileId Filepath
5 \\aa\\bb\3.jpg
6 \\aa\\bb\2.jpg

Mark as Answer if its helpful to you


Responses

Posted by: Rajnilari2015 on: 3/30/2017 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
Try this

CREATE TABLE #temp1
(
FileID INT,
Filepath NVARCHAR(255)
)

INSERT INTO #temp1 VALUES(1,'\\aa\\bb\1.jpg')
INSERT INTO #temp1 VALUES(2,'\\aa\\bb\2.jpg')
INSERT INTO #temp1 VALUES(3,'\\aa\\bb\3.jpg')
INSERT INTO #temp1 VALUES(4,'\\aa\\bb\4.jpg')
INSERT INTO #temp1 VALUES(5,'\\aa\\bb\3.jpg')
INSERT INTO #temp1 VALUES(6,'\\aa\\bb\2.jpg')

SELECT X.FileID,X.Filepath
FROM
(SELECT
FileID
,Filepath
,SlNo = ROW_NUMBER()OVER(PARTITION BY Filepath ORDER BY FileID ASC)
FROM #temp1)X WHERE X.SlNo>1 ORDER BY 1

DROP TABLE #temp1


/*
FileID	Filepath
5 \\aa\\bb\3.jpg
6 \\aa\\bb\2.jpg


*/

--
Thanks & Regards,
RNA Team

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response