-1

enter image description here

I am a new oracle developer and I have no clue how to do it: so I need to create a query in oracle that reduce the amount of my returned data what I have is 2 records for the same purpose

In the first record I have 

id1 = A and id2 = B

and in the second I have 

id1 = B and id2 = A

So I need a query that return just one record for each 2 records saved in the database have this combination.

I tried to do inner select or try to read from the same table twice and filter the results but with no success.

slavoo
  • 5,798
  • 64
  • 37
  • 39
Neeldz
  • 131
  • 2
  • 13
  • 1
    Please post some sample data, expected result and what you tried so far – Aleksej Sep 21 '16 at 10:49
  • you need to provide more information, sample data, expected results etc. – PKey Sep 21 '16 at 11:03
  • i did add a screen shot that explain how the duplication is taking place and i want for every combination of those two ids to get just 1 record – Neeldz Sep 21 '16 at 11:09
  • 1
    Data and queries should be added as formated text, not images, so that people can use them to build an example and help you – Aleksej Sep 21 '16 at 11:11

2 Answers2

0

Without having details about your table structure or the query which generated the two record result set, I can offer the following query:

SELECT LEAST(id1, id2)    AS id1,
       GREATEST(id1, id2) AS id2
FROM yourTable
GROUP BY LEAST(id1, id2),
         GREATEST(id1, id2)
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • i did add a screen shot that explain how the duplication is taking place and i want for every combination of those two ids to get just 1 record – Neeldz Sep 21 '16 at 11:08
  • @Neeldz Your query is _requesting_ that this duplication take place. But my answer actually still stands to solve your problem. – Tim Biegeleisen Sep 21 '16 at 11:10
0

If I understand well your need, you can try the following:

with test(id1, id2) as (
    select 1, 2 from dual union all
    select 2, 1 from dual
)
select least(id1, id2), greatest(id1, id2)
from test
group by least(id1, id2), greatest(id1, id2)
Aleksej
  • 22,443
  • 5
  • 33
  • 38
  • 2
    This looks much like my answer. – Tim Biegeleisen Sep 21 '16 at 11:30
  • @TimBiegeleisen: I only saw that your answer had not group by, so I thought to give mine; now I see your edit and I understand that your first version was due to the unclear question. Sorry, I'm removing soon – Aleksej Sep 21 '16 at 11:36
  • @Neeldz: Read the above comments and please accept Tim's answer; . I'm going to remove mine – Aleksej Sep 21 '16 at 11:42
  • @Aleksej i did accept Tim's answer its just i wanted to be fair since his response had duplication in the returned results . anw thank you both for your help it was really in place – Neeldz Sep 30 '16 at 12:46