I have a t1( c1 int, c2 int, c3 int ) I have to return distinct rows that the combination of values is unique, no matter with column includes the value. example: 1,2,3 1,3,2 3,2,1 all these rows are the same, so the query might return one row only

select distinct a,b,c from ( select c1, c2, c3, a = case when c1 >= c2 and c1 >= c3 then c1 when c2 >= c1 and c2 >= c3 then c2 else c3 end , b = case when c1 >= c2 and c1 <= c3 or c1 <= c2 and c1 >= c3 then c1 when c2 >= c1 and c2 <= c3 or c2 >= c3 and c2 <= c1 then c2 else c3 end , c = case when c1 <= c2 and c1 <= c3 then c1 when c2 <= c1 and c2 <= c3 then c2 else c3 end from t1 ) a

========================================== Cursors are useful if you don't know sql. DTS can be used in a similar way. Beer is not cold and it isn't fizzy.

Declare @tbl Table
(
Col1 int,
Col2 int,
Col3 int
)
Insert @tbl
Select 1,2,3 Union All
Select 1,3,2 Union All
Select 3,2,1 Union All
Select 3,2,1 Union All
Select 4,5,6 Union All
Select 5,4,6 Union All
Select 6,4,5
Select A.Col1,A.Col2,A.Col3 From
(
Select Col1+Col2+Col3 Tmp , * From @Tbl Group by Col1,Col2,Col3
) As A
Where A.Col1 = (Select Max(Col1) From @Tbl As B Where A.Tmp = Col1+Col2+Col3)

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

========================================== Cursors are useful if you don't know sql. DTS can be used in a similar way. Beer is not cold and it isn't fizzy.

also your A.Tmp would think that 1,4,6 and 2,4,5 were the same row.

========================================== Cursors are useful if you don't know sql. DTS can be used in a similar way. Beer is not cold and it isn't fizzy.

also your A.Tmp would think that 1,4,6 and 2,4,5 were the same row.

========================================== Cursors are useful if you don't know sql. DTS can be used in a similar way. Beer is not cold and it isn't fizzy.

yeah just analysed that too

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

I'm sure if we wait Mr Fribble will turn up with something tricky.

========================================== Cursors are useful if you don't know sql. DTS can be used in a similar way. Beer is not cold and it isn't fizzy.

I dont know about the performance of this query and also feasiblity..but i guess it gives right output ..

CREATE FUNCTION [dbo].[CrackInRows] (@delim varchar(1), @Phrase2Crack as varchar(8000))
RETURNS @CrackRow table
(
INROWS varchar(1000)
)
as
BEGIN
insert @CrackRow
Select NullIf(SubString(@Delim + @Phrase2Crack + @Delim , IDNos ,
CharIndex(@Delim , @Delim + @Phrase2Crack + @Delim , IDNos) - IDNos) , '') AS INROW
FROM IDNos
WHERE IDNos <= Len(@Delim + @Phrase2Crack + @Delim) AND
SubString(@Delim + @Phrase2Crack + @Delim , IDNos - 1, 1) = @Delim
AND CharIndex(@Delim , @Delim + @Phrase2Crack + @Delim , IDNos) - IDNos > 0
return
END
GO
Create Function Dbo.InCorrectOrder(@Val Varchar(100))
Returns Varchar(1000)
As
Begin
Declare @str varchar(1000)
Set @str = ''
Select @str = @str + InRows + ',' From Dbo.CrackInRows(',',@Val) Order by InRows
Select @str = left(@str,len(@str)-1)
return @str
End
GO
Declare @tbl Table
(
Col1 int,
Col2 int,
Col3 int
)
Insert @tbl
Select 1,2,3 Union All
Select 1,3,2 Union All
Select 3,2,1 Union All
Select 3,2,1 Union All
Select 4,5,6 Union All
Select 5,4,6 Union All
Select 6,4,5 Union All
Select 6,5,4 Union All
Select 1,4,6 Union All
Select 2,4,5
Select A.Col1,A.Col2,A.Col3 From
(
Select Dbo.InCorrectOrder(Convert(varchar(10),Col1)+','+Convert(varchar(10),Col2)+','+Convert(varchar(10),Col3)) As Tmp , *,
Cast(Convert(varchar(10),Col1) + Convert(varchar(10),Col2) + Convert(varchar(10),Col3) As Int) as Tmp2
From @Tbl Group by Col1,Col2,Col3
) As A
Where A.Tmp2 =
(Select Max(Cast(Convert(varchar(10),Col1) + Convert(varchar(10),Col2) + Convert(varchar(10),Col3) As Int))
From @Tbl As B
Where
A.Tmp =Dbo.InCorrectOrder(Convert(varchar(10),Col1)+','+Convert(varchar(10),Col2)+','+Convert(varchar(10),Col3)))

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

Here's another approach. It's very simple but there might be a small chance it could give wrong results - how small and whether it could be 0, I'm not sure

Something like this...

--data
Declare @t Table (c1 int, c2 int, c3 int)
Insert @t
Select 1,2,3 Union All
Select 1,3,2 Union All
Select 3,2,1 Union All
Select 3,2,1 Union All
Select 4,5,6 Union All
Select 5,4,6 Union All
Select 6,4,5
--calculation
select * from @t a
where round(rand(c1) + rand(c2) + rand(c3), 15) =
(select round(rand(c1) + rand(c2) + rand(c3), 15) from @t
group by round(rand(c1) + rand(c2) + rand(c3), 15)
having min(c1) = a.c1 and max(c3) = a.c3)

was just testing and found out somthing intresting hope you can fix it in ur next release..

Declare @t Table (c1 int, c2 int, c3 int)
Insert @t
Select 1,2,3 Union All
Select 1,3,2 Union All
Select 3,2,1 Union All
Select 3,2,1 Union All
Select 4,5,6 Union All
Select 5,4,6 Union All
Select 6,4,5 Union All
Select 6,5,4 Union All
Select 1,4,6 Union All --- Take a look at this values
Select 2,4,5 Union All -- Col1 value is incremented and Col3 values is
Select 3,4,4 Union All --Descremented by 1 ..
Select 4,4,3
--calculation
select * from @t a
where round(rand(c1) + rand(c2) + rand(c3), 15) =
(select round(rand(c1) + rand(c2) + rand(c3), 15) from @t
group by round(rand(c1) + rand(c2) + rand(c3), 15)
having min(c1) = a.c1 and max(c3) = a.c3)

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

hmmm select distinct a = min(x), b = max(x), c = sum(x) - max(x) - min(x) from ( select x = c1, c1, c2, c3 from #a union all select c2, c1, c2, c3 from #a union all select c3, c1, c2, c3 from #a ) x group by c1, c2, c3

========================================== Cursors are useful if you don't know sql. DTS can be used in a similar way. Beer is not cold and it isn't fizzy.

hmmm select distinct a = min(x), b = max(x), c = sum(x) - max(x) - min(x) from ( select x = c1, c1, c2, c3 from #a union all select c2, c1, c2, c3 from #a union all select c3, c1, c2, c3 from #a ) x group by c1, c2, c3

========================================== Cursors are useful if you don't know sql. DTS can be used in a similar way. Beer is not cold and it isn't fizzy.

That's the one! Nice work...

I think we might need to move the distinct, otherwise it doesn't work on our duplicate 3, 2, 1, but that's it. And I prefer the results in ascending order, so...

--data
Declare @t Table (c1 int, c2 int, c3 int)
Insert @t
Select 1,2,3 Union All
Select 1,3,2 Union All
Select 3,2,1 Union All
Select 3,2,1 Union All
Select 4,5,6 Union All
Select 5,4,6 Union All
Select 6,4,5 Union All
Select 1,4,6 Union All
Select 2,4,5
--calculation
select distinct a = min(x), b = sum(x) - max(x) - min(x), c = max(x)
from
(
select distinct x = c1, c1, c2, c3 from @t
union all
select distinct c2, c1, c2, c3 from @t
union all
select distinct c3, c1, c2, c3 from @t
) x
group by c1, c2, c3

Bit of a shame it only works for numerics and a maximum of 3 columns. Wonder if it could be extended to an arbitrary number of character columns.

========================================== Cursors are useful if you don't know sql. DTS can be used in a similar way. Beer is not cold and it isn't fizzy.

Well, here's another effort which should work for characters and more than 3 columns

The one issue is that I had to 'cheat' and add a row identifier to our data

--data
Declare @t Table (i int identity(1, 1), c1 int, c2 int, c3 int)
Insert @t
Select 1,2,3 Union All
Select 1,3,2 Union All
Select 3,2,1 Union All
Select 3,2,1 Union All
Select 4,5,6 Union All
Select 5,4,6 Union All
Select 6,4,5 Union All
Select 6,5,4 Union All
Select 1,4,6 Union All
Select 2,4,5 Union All
Select 3,4,4 Union All
Select 4,4,3
--calculation
select c1, c2, c3 from @t where i in (
select distinct min(i) from (
select a.i as i, b.i as j from
(
select distinct i, c1 as x from @t
union all
select distinct i, c2 from @t
union all
select distinct i, c3 from @t
) a inner join
(
select distinct i, c1 as x from @t
union all
select distinct i, c2 from @t
union all
select distinct i, c3 from @t
) b on a.x = b.x
group by a.i, b.i
having count(*) >= 3) c
group by j)
order by c1, c2, c3
--results
c1 c2 c3
----------- ----------- -----------
1 2 3
1 4 6
2 4 5
3 4 4
4 5 6

This will do numbers, characters and strings.... Kind of ugly, and doens't lend itself to adding columns very easily, but it is very fast... Taking the shown insert set and copying it 10 times, I still get a run time of 0 seconds, testing Ryan's code above on the same dataset takes 21 seconds. It also sorts the numbers as strings, so 1,11,2,3,33 :( but that could be worked around if needed...

Declare @t Table (c1 varchar(10), c2 varchar(10), c3 varchar(10))
Insert @t
Select '1','2','3' Union All
Select '1','3','2' Union All
Select '3','2','1' Union All
Select '3','2','1' Union All
Select '4','5','6' Union All
Select '5','4','6' Union All
Select '6','4','5' Union All
Select '6','5','4' Union All
Select '11','4','6' Union All
Select '2','4','5' Union All
Select '3','4','4' Union All
Select '4','4','3' Union All
Select 'a','b','c' Union All
Select 'b','c','a' Union All
Select 'c','a','d' Union All
Select 'Alan','Bob','Char' Union All
Select 'Bob','Char','Alan' Union All
Select 'Barb','Char','Alan' Union All
Select 'Char','Doug','Erik'
select distinct
cs1 = (case when c1<c2 then (case when c1<c3 then c1 else c3 end) else (case when c2<c3 then c2 else c3 end) end),
cs2 = (case when c1<c2 then (case when c2<c3 then c2 else (case when c1<c3 then c3 else c1 end) end) else (case when c1<c3 then c1 else (case when c2<c3 then c3 else c2 end) end) end),
cs3 = (case when c1>c2 then (case when c1>c3 then c1 else c3 end) else (case when c2>c3 then c2 else c3 end) end)
from @t
order by cs1,cs2,cs3
--results
cs1 cs2 cs3
---------- ---------- ----------
1 2 3
1 4 6
11 4 6
2 4 5
3 4 4
4 5 6
a b c
a c d
Alan Barb Char
Alan Bob Char
Char Doug Erik
(11 row(s) affected)

That was the solution I was trying to avoid. With a few more columns it gets very long.

With v2005 it becomes a fairly trivial problem I think.

========================================== Cursors are useful if you don't know sql. DTS can be used in a similar way. Beer is not cold and it isn't fizzy.