Mysql – Order by and then Concat

concatMySQLsorting

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.

Best Answer

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

Related Solutions

Mysql – Build a simple query and relationship table

A good starting place is to research database normalization. It explains some concepts on how to best structure tables.

More specifically, I'd name the tables "Song" (singular is often preferred) and "Artist" (probably a more central concept than a twitter account). A song would then have an ArtistID which refers to the Artist table. To get the twitter account for an artist you only need to look at the Artist table, you don't need to look at the Song table at all, e.g.:

select TwitterHandle
from Artist
where ArtistName='Beatle';

MySQL – How to Concatenate Date and Varchar

The CONCAT() function returns NULL when one of the arguments is null. You could use COALESCE() to bypass the issue, converting the nulls to empty strings - or to any other constant value, for example '00:00:00' for the time part:

select concat( coalesce(cast(transactionDate as char), ''), 
               ' ', 
               coalesce(transactionTime, '00:00:00')
             ) as transaction  
from transactionTable 
where id = 123 ;

In fact, you don't really need an explicit cast(), the cast to char is implicit:

select concat( coalesce(transactionDate, ''), 
               ' ', 
               coalesce(transactionTime, '00:00:00')
             ) as transaction  
from transactionTable 
where id = 123 ;

Best Answer

Related Solutions

Mysql – Build a simple query and relationship table

MySQL – How to Concatenate Date and Varchar

Related Question