# SELECT DISTINCT TWO columns not ranged [closed]

How can I select distinct two columns not ranged correctly. for example: if I have this table t:

``````================
|| a   || b   ||
=======||=======
|| 1   || 2   ||
|| 1   || 3   ||
|| 2   || 3   ||
|| 3   || 1   ||
|| 2   || 1   ||
================
``````

If I choose 1 in my query the result should be:

``````================
|| a   || b   ||
=======||=======
|| 1   || 2   ||
|| 1   || 3   ||
================
``````

This table is a result of selecting the rows which has a=1 or b=1 and do not repeat a,b or the inverse. Details:

The result of `SELECT * FROM t WHERE a=1 OR b=1;` will be:

``````================
|| a   || b   ||
=======||=======
|| 1   || 2   ||
|| 1   || 3   ||
|| 3   || 1   ||
|| 2   || 1   ||
================
``````

I should eliminate the repeated rows normally and inversely.

`|| 2 || 1 ||` will be eliminated because it's the inverse of `|| 1 || 2 ||`

`|| 3 || 1 ||` will be eliminated because it's the inverse of `|| 1 || 3 ||`

The result was shown above.

## closed as unclear what you're asking by Evan Carroll, Marco, McNets, LowlyDBA, Max VernonJan 22 at 20:36

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

• What database are you using? – Evan Carroll Jan 22 at 1:33
• `select distinct least(a,b), greatest(a,b) from the_table` – a_horse_with_no_name Jan 22 at 7:16

A more efficient (and somewhat shorter) version of what Jon of All Trades mentioned in his answer could be something like this:

``````SELECT DISTINCT
CASE WHEN A < B THEN A ELSE B END AS value1,
CASE WHEN A < B THEN B ELSE A END AS value2
FROM T
``````

Of course if you had to only check for tuples that contain `1`, you would add something like `WHERE A = 1 OR B = 1`

• This has another benefit: no risk of collision with the separator. If A and B are text values, some edge cases like "A|" + "" and "A" + "|" would be treated the same, whereas in your case they're always separate columns. – Jon of All Trades Jan 22 at 14:53

So the outputs should be { 1, 2 } and { 1, 3 }? I'm sure there's a more efficient way to do it, but if you just need a quick-and-dirty solution, this should work for you on SQL Server:

``````SELECT
LEFT(Concatenated, CHARINDEX('|', Concatenated) - 1),
SUBSTRING(Concatenated, CHARINDEX('|', Concatenated) + 1, 999)
FROM
(
SELECT DISTINCT
CASE
WHEN A < B
THEN CAST(A AS VARCHAR(10)) + '|' + CAST(B AS VARCHAR(10))
ELSE CAST(B AS VARCHAR(10)) + '|' + CAST(A AS VARCHAR(10))
END AS Concatenated
FROM
T
WHERE
A = 1 OR B = 1
) AS X
``````
• Thank you. give a man a fish and you feed him for a day; teach a man to fish and you feed him for a lifetime. – Safe Mode Mar 9 '16 at 20:20