OraFAQ Forum: SQL & PL/SQL » Generate unique combinations

Home » SQL & PL/SQL » SQL & PL/SQL » Generate unique combinations (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Generate unique combinations [message #646761]

Fri, 08 January 2016 07:36

Xandot
Messages: 235
Registered: January 2014
Location: India

Senior Member

Hi All,

Input Query:

with t as
(select 1 id , 'IND' team from dual
union all
select 2 id , 'ENG' team from dual
union all
select 3 id , 'AUS' team from dual)
select * from t;

Expected Output:

TeamA TeamB

IND ENG
IND AUS
ENG AUS

I want to generate unique combination of teams.

I'm trying this query:

with t as
(select 1 id , 'IND' team from dual
union all
select 2 id , 'ENG' team from dual
union all
select 3 id , 'AUS' team from dual)
select  t1.team,t2.team from t t1, t t2 
where t1.team<>t2.team;

how to eliminate duplicate rows like if IND> AUS is exist then AUS>IND should not be there.

Please help me out.

Thanks
Ankit Bhatt

Report message to a moderator

Re: Generate unique combinations [message #646767 is a reply to message #646761]

Fri, 08 January 2016 08:12

flyboy
Messages: 1903
Registered: November 2006

Senior Member

Hi,

so, stated in SQL, you want only the combinations for which T1.TEAM > T2.TEAM?

Then, just change the WHERE condition to this one as now you have inequality (<>) there.

Report message to a moderator

Re: Generate unique combinations [message #646768 is a reply to message #646767]

Fri, 08 January 2016 08:15

Xandot
Messages: 235
Registered: January 2014
Location: India

Senior Member

Thanks Flyboy..

Report message to a moderator

Previous Topic:	Case statement for multiple options
Next Topic:	Pull 20 records for each time

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 20:24:46 CDT 2024