0

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 Vernon Jan 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.

  • 1
    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
4

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

  • 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
1

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.