How do I exclude the same combination in the table in sqlserver2008

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

, +

I have a table where there are certain combinations of numbers
MyTable:
col1 col2 priority
1 2 1
1 3 2
1 4 3
2 3 2
2 4 3
2 1 1
3 6 1
3 5 3
3 1 2
so..on
And i need to exclude the combination of {(1,2) or (2,1)} and {(1,3) or (3,1)} from mytable

But I am not able to exclude the duplicate combination of the numbers.
Please suggest me a better way to exclude duplicate combinations like 
once (1,2) combination has appeared in mytable, then it should not appear again as (2,1) combination.
How can it be excluded.Please suggest.
Thanks in Advance.

What I have tried:

I have tried using ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY Col1,col3 DESC)

Posted 16-Mar-17 23:39pm

pn46

Updated 19-Mar-17 23:18pm

Add a Solution

Comments

CHill60 17-Mar-17 8:57am

Are you saying that you don't want to insert data into your table if the col1 / col2 combination already exists?

pn46 20-Mar-17 1:29am

Hi,
Yes, I don't want to insert the same combinational column data again in the table, As once the table has already with a combination, it should not insert the duplicate combination again.
Please suggest how can i avoid such duplications.?

2 solutions

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)

NightWizzard · Answer 1 · 2017-03-17T06:09:00

Solution 1

You can add a column to your query where you multiply the values from col1 and col2, then use OVER PARTITION or DISTINCT to get only those rows which don't have the same multiplication result.

Posted 17-Mar-17 6:09am

NightWizzard

Comments

pn46 20-Mar-17 1:38am

Thanks alot. It has given much better results, but what if the combination has different values and the new column gives the same result, and while using PARTITION the column will skip to next row_number. Any other way to also check the duplicate combination ?
Thanks in Advance :)

NightWizzard 20-Mar-17 8:20am

OK, I didn't assume that the occuring values may lead to same results from differing values. In this case you have to use any other numeric trick that leads to identically results where the order of the values in col1 and col2 does no matter. You may use a calculated column that concatenates the 2 values into a varchar, beginning with the lower value, convert the resulting string back to int as the result. In this case, the value order does no matter and you have a unique value for each combination that can be used for a DISTINCT query. Example for the formula of the calculated column:
CAST(CASE WHEN (col1 < col2) THEN CAST(col1 AS varchar)) + CAST(col2 AS varchar)) ELSE CAST(col2 AS varchar)) + CAST(col1 AS varchar)) AS int

CHill60 · Answer 2 · 2017-03-19T23:18:00

I may be oversimplifying this but why not just query for the combo before adding a new row?

SQL

declare @n1 int = 2
declare @n2 int = 1
declare @p int = 21

IF NOT EXISTS (SELECT 1 FROM MyTable 
	WHERE (col1 = @n1 AND col2 = @n2) 
	   OR (col1 = @n2 AND col2 = @n1))
BEGIN
	INSERT INTO MyTable VALUES (@n1,@n2,@p)
END
ELSE
BEGIN
	UPDATE MyTable SET [priority]=@p
		WHERE (col1 = @n1 AND col2 = @n2) 
	   OR (col1 = @n2 AND col2 = @n1)
	-- I've updated. Don't know what you want to do here
END