Skip to content
Advertisement

SQL query to extract matching diagonal pairs in SQL Server database

I have a database table (mytable) with 2 columns x and y as shown below, from which I intend to extract rows with matching diagonal pairs of (x,y) and (y,x) e.g., 4 21 and 21 4

I have tried the accepted code on stackoverflow here (enter link description here) on my mytable which gives me the expected results on Oracle DB.

Now I need to execute the same query on MS SQL DB but according to my understanding MS SQL DB does not support the least and greatest functions. I have tried to use the case conditions, for instance for the first part of the SQL query on the link provided, I am considering the below but so far I cannot replicate similar results:

Any suggestions on what I need to consider to complete the query in SQL Server database, so that I produce the final output as below?

It would also be great if somebody has an idea on how I can use SQL’s lag() function to assist me in achieving the same result. For instance I am trying something like below.

Expected output:

Advertisement

Answer

The equivalent of the functions LEAST() and GREATEST() is to use CASE expressions:

The above query will return a row for a combination of (x, y) that exists twice even if (y, x) does not exist.

If this is not what you want, use a self join and UNION ALL:

See the demo.

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement