Need an ORACLE SQL query to get distinct combination of values

Question

Please help on this query :(

COL X   COL Y
1       A
1       B
1       C
2       A
2       B
2       C
3       A
3       B
3       C

The output should be

COL X  COL Y
1       A or B or C

If 1 picks A , Then 2 Should not pick A (It can pick B or C) , If 2 Picks B then 3 should not pick B.

The out put should be like

1 A
2 B
3 C

or

1 C
2 A
3 B

or

...

the post is poorly formatted and difficult to read. You can use Ctrl+K to format code parts for readability — Adrian Nasui
– Adrian Nasui, Commented Nov 28, 2014 at 17:21
There are A, B and C for each and every X -- or are some variations possible (say 3A, 3B but no 3C ?) — Sylvain Leroux
– Sylvain Leroux, Commented Nov 28, 2014 at 17:59

RichardKugler · Accepted Answer · 2014-11-28 18:15:41Z

1

My first post here but I'll answer using a postgres db answer.

select 
  col1,
  col2
from
 (select row_number() over() rn1 , col1 from sample1 group by 2)a
inner join 
 (select row_number() over() rn2, col2 from sample1 group by 2)b
on a.rn1 = b.rn2
order by 1;

This returns the result set per your specification.

answered Nov 28, 2014 at 18:15

RichardKugler

111 bronze badge

Sign up to request clarification or add additional context in comments.

2 Comments

Hawx Over a year ago

Thank for the post .But I am getting this error.ORA-30485: missing ORDER BY expression in the window specification 30485. 00000 - "missing ORDER BY expression in the window specification" *Cause: Either the ORDER BY expression is mandatory for this function, or there is an aggregation group without any ORDER by expression. *Action: Error at Line: 92 Column: 10

RichardKugler Over a year ago

You can see what I'm doing here: sqlfiddle.com/#!15/e8a0b/2 note that different databases have requirements for syntax. I'm using postgres in the answer above.

Gordon Linoff · Accepted Answer · 2014-11-28 17:26:29Z

0

If you want a distinct value for column B and an arbitrary value for A, then you can do:

select MIN(A), B
from table t
group by B;

If you actually want distinct values for both A and B, then learn a bit about bipartite graphs. This is, in particular, the matching algorithm. I don't know if there is a non-recursive way to find such a set in SQL.

answered Nov 28, 2014 at 17:26

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Hawx Over a year ago

Thank you for the reply.But your query does not work, it gives 1 A,1 B,1 C :(

Sylvain Leroux · Accepted Answer · 2014-11-28 18:12:56Z

0

Assuming your data are somehow "fixed", you can build all combinations using a CROSS JOIN. The query is rather complicated because of your sub-optimal data representation:

SELECT t1."COL X" as x1, t1."COL Y" as y1,
       t2."COL X" as x2, t2."COL Y" as y2,
       t3."COL X" as x3, t3."COL Y" as y3
FROM T t1 CROSS JOIN T t2 CROSS JOIN T t3
WHERE t1."COL Y" <> t2."COL Y"
  AND t1."COL X" <> t2."COL X"
  AND t2."COL Y" <> t3."COL Y"
  AND t2."COL X" <> t3."COL X"
  AND t3."COL Y" <> t1."COL Y"
  AND t3."COL X" <> t1."COL X";

See http://sqlfiddle.com/#!4/f9bc0c/4

edited Nov 28, 2014 at 18:12

answered Nov 28, 2014 at 18:06

Sylvain Leroux

52.3k8 gold badges114 silver badges136 bronze badges

Comments

shruti · Accepted Answer · 2014-11-29 12:00:19Z

0

try this in MYSQL :

set @rank:=1;
select n.X,n.Y from
(select a.SN as SN1,b.SN as SN2,a.X as X,a.Y as Y,(case when a.X=b.X then @rank:=@rank+1 else       @rank:=1 end) as ra from
(select @rownum:=@rownum+1 as SN,X,Y from temp,(select @rownum:=0) r)a left outer join
(select @rownum1:=@rownum1+1 as SN,X,Y from temp,(select @rownum1:=0) s)b
on a.SN-1=b.SN) n
where n.X=n.ra;

answered Nov 29, 2014 at 12:00

shruti

7475 silver badges7 bronze badges

Collectives™ on Stack Overflow

Need an ORACLE SQL query to get distinct combination of values

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related