How to show all duplication row in data grid only

Question

0.00/5 (No votes)

See more:

Dear:
friends

please im not good in asking questions

i have one table (id , Civilidd , name , address ), and have duplication on it with Civilidd i want to make the datagridview to show me all rows of the duplicate records and i will choose what i want to deleted.

i use sql server and C#

What I have tried:

i try to do that with this code bout it gave me a row number i don't want that, i want to see the first and the second rows

SQL

<pre> with tablctc as
(
select * , ROW_NUMBER() over (partition by CIVILIDD order by CIVILIDD ) as RowNumber
from tabl1
)
select * from tablctc where RowNumber>1 AND (CIVILIDD IS NOT NULL)

warm regards

Posted 23-Dec-18 7:07am

el_tot93

Updated 23-Dec-18 13:50pm

Add a Solution

2 solutions

Solution 1

Just COUNT on CivilId.

If the COUNT > 1, you have one or more duplicates.

Posted 23-Dec-18 7:54am

Gerry Schmitz

Comments

el_tot93 23-Dec-18 13:56pm

i did that in my code ( rownumber>1 ) what i want i need to show row number 1 and 2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Bryian Tan · Accepted Answer · 2018-12-23T13:51:00

Here is an example based on Gerry recommendation. I'm guessing it based on what posted here. First, find out which row has duplicate then capture the Id. After that, query the original table which contain the duplicate id with Row_Number. Hope that help.

SQL

DECLARE @temp1 TABLE (
	Civilidd INT , 
	[Name]	VARCHAR(50) , 
	[Address]	 VARCHAR(50)
)

INSERT INTO @temp1
	SELECT 1, 'a1', 'address 1' UNION
	SELECT 1, 'a1 x 2', 'address 1 x 2' UNION
	SELECT 1, 'a1 x 3', 'address 1 x 3' UNION
	SELECT 2, 'a2', 'address 1' UNION
	SELECT 3, 'a2', 'address 1' UNION
	SELECT 3, 'a2 x 2', 'address 3 x 2' UNION
	SELECT 4, 'a4', 'address 4'

;WITH cteCount AS (
	SELECT Civilidd FROM @temp1
	GROUP BY Civilidd
	HAVING COUNT(Civilidd) > 1
) SELECT t.* , ROW_NUMBER() over (partition by t.CIVILIDD order by t.CIVILIDD ) as RowNumber
	FROM @temp1 t JOIN cteCount cc ON t.Civilidd = cc.Civilidd

Output:

Civilidd	Name	Address	        RowNumber
1	         a1	address 1	1
1	         a1 x 2	address 1 x 2	2
1	         a1 x 3	address 1 x 3	3
3	         a2	address 1	1
3	         a2 x 2	address 3 x 2	2