How do I get the below output in sql. Please help me write a query to get the expected output

Question

1.00/5 (3 votes)

See more:

x y
20 20
20 20
21 22
22 21
23 24
24 25
25 24

i have a above table with 2 columns.i need to filter out similar set of data from 2 columns and display as one set.in brief my output should be as below :

x y
20 20
21 22
24 25

basically i want records with similar set e.g 21,22 and 22,21 are considered as similar set and hence i want to display only one record as 21,22.Whereas 23,24 doesn't have similar set(24,23) in the table so i want to eliminate that record.

What I have tried:

i tried group by but it isn't working

Posted 17-Jan-18 20:06pm

Member 13629523

Updated 18-Jan-18 4:10am

v2

Add a Solution

Comments

OriginalGriff 18-Jan-18 2:18am

Sorry, but that doesn't make a lot of sense: you need to explain in better detail why some of the results from that input are included and others not: What happened to "23" for example? "22, 21" isn;t there either. Why not?

This is not a good question - we cannot work out from that little what you are trying to do.
Remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with.
Use the "Improve question" widget to edit your question and provide better information.

Member 13629523 18-Jan-18 2:35am

Have updated my question.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jörgen Andersson · Answer 1 · 2018-01-18T03:49:00

This query removes duplicates, if you want multiple identical pairs it won't work.

SQL

WITH r as (
    SELECT  X
           ,Y
           ,ROW_NUMBER() OVER (ORDER BY X,Y) rn
    FROM    t
    )
SELECT  DISTINCT x,y
FROM    r t1
WHERE EXISTS (
    SELECT  x, y
    FROM    r t2
    WHERE   t1.x=t2.y
        AND t1.y=t2.x
        AND t1.rn<t2.rn)

SQL Fiddle[^]

debasish mishra · Answer 2 · 2018-01-17T20:28:00

Solution 1

select table1.id, table1.x, table1.y
from yourtable table1
where exists (select x, y
from yourtable table2
where table1.x=table2.y and table1.y=table2.x)
Union
select t1.x, t1.y
from yourtable t1
where exists (select x, y
from yourtable t2
where t1.x=t2.y and t1.x=t2.y)
and t1.x<t1.y

Please don't copy paste the code directly.I believe there is some syntactical issue.First resolve that and then the main logic will be with"and" "or" operators.
I have tested this solution for the second query after union in sql fiddle.Please vote it will indeed help the community.

SQL Fiddle[^]

Posted 17-Jan-18 20:28pm

debasish mishra

Updated 17-Jan-18 23:06pm

v3

Comments

Member 13629523 18-Jan-18 2:31am

i don't have id column in my table.

[no name] 18-Jan-18 2:59am

Can you remove group by and having and then give a try

Member 13629523 18-Jan-18 3:14am

not working removing having and group by also.

[no name] 18-Jan-18 3:18am

Remove condition after "OR",you'll get data like "20 20"then union

select table1.id, table1.x, table1.y
from yourtable table1
where exists (select x, y
from yourtable table2
where table1.x=table2.y and table1.y=table2.x)

and try running two separate queries before writting Union

Member 13629523 18-Jan-18 3:31am

its just removing 20,20 duplicates and giving only one entry for 20,20.Not working for other sets.

[no name] 18-Jan-18 5:04am

Final query will look like below.

select table1.id, table1.x, table1.y
from yourtable table1
where exists (select x, y
from yourtable table2
where table1.x=table2.y and table1.y=table2.x)
Union
select t1.x, t1.y
from yourtable t1
where exists (select x, y
from yourtable t2
where t1.x=t2.y and t1.x=t2.y)
and t1.x<t1.y

Updating in the solution as well.Keep coding with SQL Fiddle like i did then only you will get the solutions easily.Please vote for the solution and it will indeed help the community.