How to findout this criteria [Resolved]

Posted by Jayakumars under C# on 3/30/2017 | Points: 10 | Views : 1411 | Status : [Member] [MVP] | Replies : 1

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

--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

Kumaraspcode2009@gmail.com

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

Latest Posts