SQL Query - uniqueness in combination

Riddhi Valecha

Hi all,

I have a table as -

ID	Code	Value
1	101	abc
2	101	def
3	201	abc
4	101	def

Now,

In this table, I need to check the uniqueness in combination -

101 - abc ---> allowed.

101 - def ---> allowed.

101 - def ->> NOT ALLOWED.... Combination "101 - def" exists.

Similarly,

Combination "101 - abc" must not be allowed next time.

How to check this combination uniqueness in SQL Server ??

Please help !!

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Pankaj Kumar Choudhary11y ago
hello @Riddhi use this code for your second query

With CTE(Id,Code,value,Row1)
as
(
Select Id,Code,value, ROW_NUMBER() Over(partition by Code , Value order By Code) as Row1
from Table_name
)

Select * from CTE where Row1>1
sudipta sanyal11y ago
How to fetch the combination duplicates records from the table

CREATE table #temp_data (id INT IDENTITY(1,1), code INT, val NVARCHAR(20))
INSERT INTO #temp_data
VALUES (101,'abc')
INSERT INTO #temp_data
VALUES (101,'def')
INSERT INTO #temp_data
VALUES (201,'abc')
INSERT INTO #temp_data
VALUES (101,'abc')
INSERT INTO #temp_data
VALUES (101,'abc')
INSERT INTO #temp_data
VALUES (201,'abc')
;WITH CTE (id,code,val,dcount)
AS
(
SELECT id,
code,
val ,
ROW_NUMBER() OVER (PARTITION BY code,val ORDER BY code) AS dcount
FROM #temp_data
)
SELECT id,
code,
val
FROM CTE WHERE dcount > 1;
SELECT * FROM #temp_data
DROP TABLE #temp_data

Hello @riddhi Please Mark as "Accepted Answer" if it helps you.
Pankaj Kumar Choudhary11y ago
Hello @riddhi you can use this code for your first query

Create Trigger Insert_ On Table_name
Instead Of Insert
as
Begin
Declare @Code int
Declare @value nvarchar(max)

select @Code= tab.Code from inserted tab
select @value= tab.value from inserted tab
IF NOT EXISTS (SELECT * FROM table_name WHERE code = @Code AND value = @value)
BEGIN
INSERT INTO Table_name
VALUES (@Code,@value)
END
End
Riddhi Valecha11y ago
Hi..

Thanks a ton.... Will do this logic now !

And how to fetch the combination duplicates records from the table (with more than 5000 records ) ??

Please guide !
sudipta sanyal11y ago
CREATE table #temp_data (id INT IDENTITY(1,1), code INT, val NVARCHAR(20))
INSERT INTO #temp_data
VALUES (101,'abc')
INSERT INTO #temp_data
VALUES (101,'def')
INSERT INTO #temp_data
VALUES (201,'abc')
IF NOT EXISTS (SELECT * FROM #temp_data WHERE code = 101 AND val = 'abc')
BEGIN
INSERT INTO #temp_data
VALUES (101,'abc')
END
SELECT * FROM #temp_data
DROP TABLE #temp_data