| Author |
 Topic  |
|
noamg
Posting Yak Master
Israel
204 Posts |
 Posted - 05/24/2006 : 06:29:03
|
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
thanks
Noam
Noam Graizer |
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 05/24/2006 : 06:54:35
|
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. |
 |
|
|
chiragkhabaria
Flowing Fount of Yak Knowledge
India
1907 Posts |
Posted - 05/24/2006 : 07:22:00
|
Or
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. |
|
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 05/24/2006 : 07:25:43
|
add
Union All
Select 6,5,4
to that.
==========================================
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. |
|
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 05/24/2006 : 07:33:54
|
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. |
|
|
|
chiragkhabaria
Flowing Fount of Yak Knowledge
India
1907 Posts |
Posted - 05/24/2006 : 07:36:31
|
oh there is bug 
can only use Nigel's Methods.. 
If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them. |
|
|
|
chiragkhabaria
Flowing Fount of Yak Knowledge
India
1907 Posts |
Posted - 05/24/2006 : 07:37:41
|
quote:
Originally posted by nr
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. |
|
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 05/24/2006 : 08:19:39
|
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. |
|
|
|
chiragkhabaria
Flowing Fount of Yak Knowledge
India
1907 Posts |
Posted - 05/24/2006 : 09:03:11
|
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. |
|
|
|
RyanRandall
Flowing Fount of Yak Knowledge
United Kingdom
1074 Posts |
Posted - 05/24/2006 : 09:51:02
|
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)
Ryan Randall
www.monsoonmalabar.com London-based IT consultancy
Solutions are easy. Understanding the problem, now, that's the hard part. |
|
|
|
chiragkhabaria
Flowing Fount of Yak Knowledge
India
1907 Posts |
Posted - 05/24/2006 : 09:55:51
|
Add this
Select 1,4,6 Union All
Select 2,4,5
2,4,5 doesnt comes in the output???
If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them. |
|
|
|
chiragkhabaria
Flowing Fount of Yak Knowledge
India
1907 Posts |
Posted - 05/24/2006 : 10:04:55
|
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. |
|
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 05/24/2006 : 10:22:50
|
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. |
|
|
|
RyanRandall
Flowing Fount of Yak Knowledge
United Kingdom
1074 Posts |
Posted - 05/24/2006 : 12:14:50
|
quote:
Originally posted by nr
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
Ryan Randall
www.monsoonmalabar.com London-based IT consultancy
Solutions are easy. Understanding the problem, now, that's the hard part. |
|
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 05/24/2006 : 12:21:10
|
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. |
|
|
|
RyanRandall
Flowing Fount of Yak Knowledge
United Kingdom
1074 Posts |
Posted - 05/24/2006 : 12:22:33
|
I'm thinking about it
Ryan Randall
www.monsoonmalabar.com London-based IT consultancy
Solutions are easy. Understanding the problem, now, that's the hard part. |
|
|
|
RyanRandall
Flowing Fount of Yak Knowledge
United Kingdom
1074 Posts |
Posted - 05/24/2006 : 12:38:16
|
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
Ryan Randall
www.monsoonmalabar.com London-based IT consultancy
Solutions are easy. Understanding the problem, now, that's the hard part. |
|
|
|
samuelclay
Yak Posting Veteran
USA
71 Posts |
Posted - 05/24/2006 : 17:55:05
|
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)
|
|
|
|
chiragkhabaria
Flowing Fount of Yak Knowledge
India
1907 Posts |
Posted - 05/25/2006 : 01:28:56
|
Oh! Cool i guess this is the best solution so far unless someone comes with the real good one.
If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them. |
|
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 05/25/2006 : 05:01:46
|
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. |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16769 Posts |
Posted - 05/25/2006 : 05:18:27
|
Just an idea. How about converting the columns value to csv (sorted) and then distinct it ?
KH
|
|
|
|
Topic |
|
|
|
distinct of few columns
