2

There's a table T with columns n00, n01, n01, ..., n99, all integers.

I need to select all rows from this table where n00...n99 values are unique within each row.

Example for smaller number of columns:

columns: n0, n1, n2 row 1: 10, 20, 30 row 2: 34, 45, 56 row 3: 12, 13, 12 row 4: 31, 65, 90

I need the select statement to return rows 1, 2 and 4 but not 3 (row 3 contains non-unique value of 12 so filter it out).

Effectively I need to implement this:

select * from t where n00 <> n01 and n00 <> n02 and ... and n00 <> n99 and n01 <> n02 and n01 <> n03 and ... and n01 <> n99 and n02 <> n03 and n02 <> n04 and ... and n02 <> n99 ... and n97 <> n98 and n97 <> n99 and n98 <> n99

... but with "smarter" WHERE block.

Any hints welcome.

Improve this question

2 Answers 2

Reset to default
2

You can use UNPIVOT as well:

DECLARE @t TABLE(n0 int, n1 int, n2 int); INSERT INTO @t VALUES (10, 20, 30), (34, 45, 56), (12, 13, 12), (31, 65, 90); WITH cteRows AS( SELECT ROW_NUMBER() OVER (ORDER BY n0, n1, n2) rn, * FROM @t ), cteUP AS( SELECT rn, rn_val FROM cteRows UNPIVOT( rn_val FOR rn_vals IN(n0, n1, n2) ) up ), cteFilter AS( SELECT rn, rn_val, count(*) anz FROM cteUP GROUP BY rn, rn_val HAVING count(*) > 1 ) SELECT * FROM cteRows WHERE rn NOT IN (SELECT rn FROM cteFilter)
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I prefer your solution over John's as it does not involve internal conversions to/from XML. Both return correct results though.
2

A more dynamic approach using CROSS APPLY and a little XML. I should add UNPIVOT would be more performant, but the performance of this approach is very respectable, and you don't have identify all the fields.

You'll notice I added an ID field. Can be removed from the CROSS APPLY C if it does not exist. I included the ID to demonstrate that additional fields may be excluded from the logic.

Declare @YourTable table (id int,n0 int, n1 int, n2 int) Insert Into @YourTable values (1,10, 20, 30), (2,34, 45, 56), (3,12, 13, 12), (4,31, 65, 90) Select A.* From @YourTable A Cross Apply (Select XMLData=cast((Select A.* For XML Raw) as xml)) B Cross Apply ( Select Cnt=count(*),Uniq=count(Distinct Value) From ( Select ID = r.value('@id','int') -- case sensitive ,Item = attr.value('local-name(.)','varchar(100)') ,Value = attr.value('.','varchar(max)') From B.XMLData.nodes('/row') as A(r) Cross Apply A.r.nodes('./@*') AS B(attr) Where attr.value('local-name(.)','varchar(100)') not in ('id','excludeotherfields') -- case sensitive ) U ) C Where Cnt=Uniq

Returns

id n0 n1 n2 1 10 20 30 2 34 45 56 4 31 65 90

If it helps with the visualization, the XML portion generates the following

enter image description here

Improve this answer

2 Comments

John, I do not understand your answer but it is returning correct results. I need to go to SQL crèche now I guess. I'm choosing Tyron78's answer over yours as it does not require any XML conversions. Still, I'm amazed.
@xpil Which ever answer works best for you. Keep the XML portion in your back pocket. I've found it useful for dynamically transforming data into an EAV (Entity Attribute Value) structure.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.