Find unique combinations 1

[CassidyHunt]

I need a query that will get me all unique combination of 4 columns. Below is an example of the data:

select a1.a, b1.b, c1.c, d1.d from 
(select rownum as a from part where rownum <= 100) a1,
(select rownum as b from part where rownum <= 100) b1,
(select rownum as c from part where rownum <= 100) c1,
(select rownum as d from part where rownum <= 100) d1

A result that is 1,25,15,20 is the same combination as 20,15,25,1 as well as 25,1,20,15. I would only want one of those results returned. Now having 20,20,5,10 would be a unique combination. So repeated numbers in columns would be ok. Just not repeated sequence by row.

Anyone have an idea how to accomplish that?

As always your help is greatly appreciated.

Cassidy

 

[taupirho]

See if this works

select * from
(
select a1.a, b1.b, c1.c, d1.d from
(select rownum as a from iboxx_eom_load where rownum <=100) a1,
(select rownum as b from iboxx_eom_load where rownum <=100) b1,
(select rownum as c from iboxx_eom_load where rownum <=100) c1,
(select rownum as d from iboxx_eom_load where rownum <=100) d1
)
where mod(a,4) = 1 and mod(b,4) = 2
and mod(c,4) = 3 and mod(d,4) = 0


In order to understand recursion, you must first understand recursion.

 

[CassidyHunt]

Absolutely brilliant. Thank you so much. That works fantastic.

 

[CassidyHunt]

Only one issue I found and maybe it will be a simple tweak. 20,20,5,1 is an acceptable combination. This skips 4 for each column. I wrote a brute force solution I can post.

Let me know what you think

Cassidy

 

[Turkbear]

Hi,
Just curious Cassidy, any relation to Dave ( SantaMufasa) Hunt?



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[CassidyHunt]

No relation. I have done a lot of work with him in the past and met him in person.

 

[Turkbear]

Hi,
Thanks for the info..I have much respect for his breadth of knowledge.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[aks12]

Hello, I need help constructing a query that will read a column in a table and assign a unique value to the record (but not necessarily sequential) every time the current record does not equal the previous record. Below is a sample of data and what I'd expect the query to return. I've tried a few queries with PARTITION BY, but not having any luck getting the desired results.

Sample data set: Expected Result:

Branch_ID Branch_ID Group
1009 1009 1
1012 1012 2
1012 1012 2
1012 1012 2
1003 1003 3
1003 1003 3
1009 1009 4
1009 1009 4
1009 1009 4
1007 1007 5
1002 1002 6

 

[dkyrtata]

Try creating a virtual table in your FROM clause that will give you a distinct group-id for each branch. Then join the virtual table to the original table.

Here is a simple SELECT for the virtual table. It will give a unique group-id but it won't be consecutive as in your example. But once you get it working you can get consecutive numbers by using the "PARTITION BY" instead.

SELECT branch_id,MAX(ROWNUM) "group_id" FROM the_table GROUP BY branch_id;