Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 1 down vote accepted

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

share|improve this answer

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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