SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 distinct of few columns
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 3

noamg
Posting Yak Master

Israel
215 Posts

Posted - 05/24/2006 :  06:29:03  Show Profile  Reply with Quote
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  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 05/24/2006 :  07:22:00  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote
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.
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 05/24/2006 :  07:25:43  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 05/24/2006 :  07:33:54  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 05/24/2006 :  07:36:31  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote
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.
Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 05/24/2006 :  07:37:41  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote
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.
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 05/24/2006 :  08:19:39  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 05/24/2006 :  09:03:11  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote
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.
Go to Top of Page

RyanRandall
Flowing Fount of Yak Knowledge

United Kingdom
1074 Posts

Posted - 05/24/2006 :  09:51:02  Show Profile  Reply with Quote
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.
Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 05/24/2006 :  09:55:51  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote
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.
Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 05/24/2006 :  10:04:55  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote
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.
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 05/24/2006 :  10:22:50  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

RyanRandall
Flowing Fount of Yak Knowledge

United Kingdom
1074 Posts

Posted - 05/24/2006 :  12:14:50  Show Profile  Reply with Quote
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.
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 05/24/2006 :  12:21:10  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

RyanRandall
Flowing Fount of Yak Knowledge

United Kingdom
1074 Posts

Posted - 05/24/2006 :  12:22:33  Show Profile  Reply with Quote
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.
Go to Top of Page

RyanRandall
Flowing Fount of Yak Knowledge

United Kingdom
1074 Posts

Posted - 05/24/2006 :  12:38:16  Show Profile  Reply with Quote
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.
Go to Top of Page

samuelclay
Yak Posting Veteran

USA
71 Posts

Posted - 05/24/2006 :  17:55:05  Show Profile  Visit samuelclay's Homepage  Reply with Quote
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)
Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 05/25/2006 :  01:28:56  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote
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.
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 05/25/2006 :  05:01:46  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 05/25/2006 :  05:18:27  Show Profile  Reply with Quote
Just an idea. How about converting the columns value to csv (sorted) and then distinct it ?


KH

Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000