distinct of few columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

distinct of few columns

Author

Topic

noamg
Posting Yak Master

215 Posts

Posted - 2006-05-24 : 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

12543 Posts

Posted - 2006-05-24 : 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
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-24 : 07:22:00



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

12543 Posts

Posted - 2006-05-24 : 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

12543 Posts

Posted - 2006-05-24 : 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
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-24 : 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
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-24 : 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

12543 Posts

Posted - 2006-05-24 : 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
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-24 : 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
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-24 : 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
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-24 : 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
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-24 : 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

12543 Posts

Posted - 2006-05-24 : 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
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-24 : 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

12543 Posts

Posted - 2006-05-24 : 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
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-24 : 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
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-24 : 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

71 Posts

Posted - 2006-05-24 : 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
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-25 : 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

12543 Posts

Posted - 2006-05-25 : 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)

17689 Posts

Posted - 2006-05-25 : 05:18:27

Just an idea. How about converting the columns value to csv (sorted) and then distinct it ?

Previous Page&nsp; Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources