How to extract non grouped column data?

Question

I have a table A with following data:

A:

colA colB
 a     x
 b     x
 c     y
 d     y
 e     z
 f     z

I want the output as:

colA colA_1
 a     b
 c     d
 e     f

I.e. I want to group the data based on colB and fetch the values from colA. I know that the same value will appear exactly twice in colB.

What I am trying to do is:

SELECT a1.colA, a2.colA
FROM A a1
JOIN A a2
ON a1.colA != a2.colA and a1.colB=a2.colB;

But this gives the output as:

colA colA_1
 a     b
 b     a
 c     d
 d     c
 e     f
 f     e

How can I fix this to get the desired output?

As a workaround, instead of `SELECT a1.colA, a2.colA` you could select `SELECT a1.colA, a2.colA, a1.colB` then, group by colB, order the rows by colA and apply the solution on this link to select the first row of each group: http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group — Rumpelstinsk, Oct 14 '16 at 07:28

score 3 · Accepted Answer · answered Oct 14 '16 at 07:26

3

No need to join, simply do a GROUP BY:

SELECT min(colA), max(colA)
FROM A
group by colB

answered Oct 14 '16 at 07:26

jarlh

1 Answers1