1

I have a table table1 like:

col1  col2

A1    A2
A1    B1
A1    B2
A2    A1
A2    B1
A2    B2
B1    A1
B1    A2
B1    B2
B2    A1
B2    A2
B2    B1

I need to concat col1 and col2 such a way that it should be sorted first.

Expected result:

col1  col2  ConcatResult

A1    A2    A1-A2
A1    B1    A1-B1
A1    B2    A1-B2
A2    A1    A1-A2
A2    B1    A2-B1
A2    B2    A2-B2
B1    A1    A1-B1
B1    A2    A2-B1
B1    B2    B1-B2
B2    A1    A1-B2
B2    A2    A2-B2
B2    B1    B1-B2

One more requirement: if possible, I would like to select the distinct from ConcatResult column.

1

You can do

SELECT col1, col2,
       CONCAT(LEAST(col1, col2), '-', 
              GREATEST(col1, col2)) concat_result
  FROM table1
 ORDER BY concat_result

Output:

| COL1 | COL2 | CONCAT_RESULT |
|------|------|---------------|
|   A1 |   A2 |         A1-A2 |
|   A2 |   A1 |         A1-A2 |
|   B1 |   A1 |         A1-B1 |
|   A1 |   B1 |         A1-B1 |
|   A1 |   B2 |         A1-B2 |
|   B2 |   A1 |         A1-B2 |
|   A2 |   B1 |         A2-B1 |
|   B1 |   A2 |         A2-B1 |
|   A2 |   B2 |         A2-B2 |
|   B2 |   A2 |         A2-B2 |
|   B1 |   B2 |         B1-B2 |
|   B2 |   B1 |         B1-B2 |

To get distinct values from concatenated result

SELECT DISTINCT 
       CONCAT(LEAST(col1, col2), '-', 
              GREATEST(col1, col2)) concat_result
  FROM table1
 ORDER BY concat_result

Output:

| CONCAT_RESULT |
|---------------|
|         A1-A2 |
|         A1-B1 |
|         A1-B2 |
|         A2-B1 |
|         A2-B2 |
|         B1-B2 |

Here is SQLFiddle demo

1

Might be done with CASE expression.

Also MySQL allows to compare string binary representations with <, > and = operators. Another possibility is STRCMP() function.

SELECT
    `col1`,
    `col2`,
    CASE
        WHEN `col1` > `col2` THEN CONCAT_WS('-', `col2`, `col1`)
        WHEN `col1` < `col2` THEN CONCAT_WS('-', `col1`, `col2`)
    END as `result`
FROM
    -- replace bottom part with table name.
    -- I've used UNION ALL for debugging.
    (
        SELECT 'A1' as `col1`, 'A2' as `col2` UNION ALL
        SELECT 'A1' as `col1`, 'B1' as `col2` UNION ALL
        SELECT 'A1' as `col1`, 'B2' as `col2` UNION ALL
        SELECT 'A2' as `col1`, 'A1' as `col2` UNION ALL
        SELECT 'A2' as `col1`, 'B1' as `col2` UNION ALL
        SELECT 'A2' as `col1`, 'B2' as `col2` UNION ALL
        SELECT 'B1' as `col1`, 'A1' as `col2` UNION ALL
        SELECT 'B1' as `col1`, 'A2' as `col2` UNION ALL
        SELECT 'B1' as `col1`, 'B2' as `col2` UNION ALL
        SELECT 'B2' as `col1`, 'A1' as `col2` UNION ALL
        SELECT 'B2' as `col1`, 'A2' as `col2` UNION ALL
        SELECT 'B2' as `col1`, 'B1' as `col2`
    ) `sub`

Result:

col1 col2 result
----------------
A1   A2   A1-A2
A1   B1   A1-B1
A1   B2   A1-B2
A2   A1   A1-A2
A2   B1   A2-B1
A2   B2   A2-B2
B1   A1   A1-B1
B1   A2   A2-B1
B1   B2   B1-B2
B2   A1   A1-B2
B2   A2   A2-B2
B2   B1   B1-B2

For DISTINCT case, it would be needed to exclude col1 and col2 from SELECT statement:

SELECT DISTINCT
    CASE
        WHEN `col1` > `col2` THEN CONCAT_WS('-', `col2`, `col1`)
        WHEN `col1` < `col2` THEN CONCAT_WS('-', `col1`, `col2`)
    END as `result`
FROM
    `YourTable`;

Result:

result
------
A1-A2
A1-B1
A1-B2
A2-B1
A2-B2
B1-B2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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