Panky26 Panky26 - 3 months ago 14
SQL Question

Query to get row of unique value from sql table in MS SQL Server (Database)

Here I have two columns like below example column1 & column2 in sql table and i want to get unique row value on the basis of column2 column value from table

Below example of dummy table

Column1 Column2

---------- -----------

1001 ab

1001 abc

1001 abcd

2001 wxyz

2001 wxy

2001 wx


In above example value starting from a & another value starting from w in Column2

On the basis of same value max length, i want to get result like below

Output:

Column1 Column2

---------- -----------

1001 abcd

2001 wxyz

Answer

If you are looking if your values in column2 are somewhere included in other rows, in other words: If you are looking for rows with combinations of characters which are unique on their own, this might be your solution:

CREATE TABLE TestTable(Column1 INT,Column2 VARCHAR(100));
INSERT INTO TestTable VALUES
 (1001,'ab')
,(1001,'abc')
,(1001,'abcd')
,(2001,'wxyz')
,(2001,'xyz')
,(2001,'yz');

SELECT *
FROM TestTable
WHERE NOT EXISTS(SELECT 1 
                 FROM TestTable AS x 
                 WHERE x.Column1=TestTable.Column1 
                   AND LEN(x.Column2)>LEN(TestTable.Column2)
                   AND x.Column2 LIKE  '%' + TestTable.Column2  + '%'
                )

DROP TABLE TestTable;