CODEWITHSUNDEEP
sql
isxaker
isxaker

Reputation: 9466

Remove duplicates from inner join with one table

I have following table

id | cId
---------------
1  | 1
2  | 1
3  | 2
4  | 2

I need help with query, that return following result

id1 | id2 | cId
----------------------
1   | 2   | 1
3   | 4   | 2


SELECT s1.id firstC, s2.id secondC
FROM SingleTable s1 inner join SingleTable s2 on s1.cId = s2.cId

But i get duplicates. I want gte result without duplicates.

My result:

id1 | id2 | cId
---------------
1   | 1   | 1
2   | 1   | 1
1   | 2   | 1
2   | 2   | 1
3   | 3   | 2
4   | 3   | 2
3   | 4   | 2
4   | 4   | 2

Please help.

Upvotes: 0

Views: 43

Answers (2)

eumiro
eumiro

Reputation: 212885

Remove the duplicates (s1.id = s2.id) with an extra condition (s1.id < s2.id):

SELECT s1.id firstC, s2.id secondC
FROM SingleTable s1 INNER JOIN SingleTable s2
                    ON (s1.cId = s2.cId AND s1.id < s2.id)

Upvotes: 1

juergen d
juergen d

Reputation: 204766

You can just group by the cId and then select the highest and lowest id for every cId

SELECT cId, 
       min(id) as id1, 
       max(id) as id2
FROM SingleTable 
GROUP BY cId

Upvotes: 1

Related Questions